Hello everybody!
System description:
OS: Windows Server 2003 Enterprise Edition SP1
RAM: 16 Gb
SQL: SQL Server 2000 Enterprise Edition SP3
SQL Server has "awe enabled"=1 and "set working set size"=1 at the same time
.
"Max server memory" is limited by 12 Gb. SQLServer:MemoryManager:Total
Server Memory counter shows that SQL Server consumes all of the 12 Gb memory
.
Allmost every day the client receives ODBC error 1222 (lock timeout) even if
only one user is connected to the database.
BOL says that if "awe enabled"=1 memory is not swapped out by default.
www.microsoft.com says that if you change "set working set size" from its
default value it may involve 844 and 845 errors (page latch timeout).
So the questions are:
1. Can two simulaneously enabled options ("awe enabled" and "set working set
size") result in such conflict?
2. Can enabling set working set size result in ODBC error 1222?
Thank youI don't know if that configuration will cause that error but I doubt it.
There is no reason to have SET Working Set Size set to 1 when you have AWE
since AWE in 2000 is not dynamic to begin with. AWE also allocates all the
memory up to the Max memory setting on startup. I would turn it off and see
if the errors go away and I see no need to have in on anyway.
Andrew J. Kelly SQL MVP
"RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
news:CFCDD61D-ACE4-4C25-ABF1-50395C763B8E@.microsoft.com...
> Hello everybody!
> System description:
> OS: Windows Server 2003 Enterprise Edition SP1
> RAM: 16 Gb
> SQL: SQL Server 2000 Enterprise Edition SP3
> SQL Server has "awe enabled"=1 and "set working set size"=1 at the same
> time.
> "Max server memory" is limited by 12 Gb. SQLServer:MemoryManager:Total
> Server Memory counter shows that SQL Server consumes all of the 12 Gb
> memory.
> Allmost every day the client receives ODBC error 1222 (lock timeout) even
> if
> only one user is connected to the database.
> BOL says that if "awe enabled"=1 memory is not swapped out by default.
> www.microsoft.com says that if you change "set working set size" from its
> default value it may involve 844 and 845 errors (page latch timeout).
> So the questions are:
> 1. Can two simulaneously enabled options ("awe enabled" and "set working
> set
> size") result in such conflict?
> 2. Can enabling set working set size result in ODBC error 1222?
> Thank you
>|||Thank you, Mr.Kelly. Before I read you answer I had decided to act by trial
and error and I advice to turn "working set size" off. My client informed me
that the error did not disappear but it got less frequent.|||The Lock Timeout setting is infinite by default in SQL Server. IF they are
getting this message that means someone somewhere changed the setting. I
would look for any code that has SET LOCK_TIMEOUT in it and find out why and
what value they set it to.
Andrew J. Kelly SQL MVP
"RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
news:69717477-0287-4FB8-9F65-96168F25F2BB@.microsoft.com...
> Thank you, Mr.Kelly. Before I read you answer I had decided to act by
> trial
> and error and I advice to turn "working set size" off. My client informed
> me
> that the error did not disappear but it got less frequent.|||Yes, I see, but can only one user working with the server lock himself? I
mean there are situations when only one user works with the database but the
error occurs all the same.
"Andrew J. Kelly" wrote:
> The Lock Timeout setting is infinite by default in SQL Server. IF they are
> getting this message that means someone somewhere changed the setting. I
> would look for any code that has SET LOCK_TIMEOUT in it and find out why a
nd
> what value they set it to.
> --
> Andrew J. Kelly SQL MVP
>
> "RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
> news:69717477-0287-4FB8-9F65-96168F25F2BB@.microsoft.com...
>
>|||Sure. If the user is trying to get an exclusive table lock there are lots of
things that can prevent him from getting one. I think you need to see the
code and why they are setting this and under what conditions it is
happening.
Andrew J. Kelly SQL MVP
"RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
news:057E64D8-ED9F-4442-9623-FECE3BDB865E@.microsoft.com...[vbcol=seagreen]
> Yes, I see, but can only one user working with the server lock himself? I
> mean there are situations when only one user works with the database but
> the
> error occurs all the same.
> "Andrew J. Kelly" wrote:
>|||There is a general interface setting which allow users to set time-out (via
the setting the code receives time interval for SET LOCK_TIMEOUT statement).
The only thing I'm going to recommend is to increase the value. Thank you,
Mr.Kelly, you have been very helpful.
"Andrew J. Kelly" wrote:
> Sure. If the user is trying to get an exclusive table lock there are lots
of
> things that can prevent him from getting one. I think you need to see the
> code and why they are setting this and under what conditions it is
> happening.
> --
> Andrew J. Kelly SQL MVP
>
> "RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
> news:057E64D8-ED9F-4442-9623-FECE3BDB865E@.microsoft.com...
>
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment