July 2, 2008 at 2:43 pm
One of my 64-bit sql sever 2000 enterprise(SP3) cluster machine with 8 GB RAM is getting rebooted automatically.
After going thru sql error logs I found the following:
2008-06-25 01:32:47.10 spid1 Query Memory Manager: Grants=25 Waiting=0 Maximum=328809 Available=231519,0
2008-06-25 01:32:55.74 server Error: 17803, Severity: 20, State: 14,0
2008-06-25 01:32:55.74 server Insufficient memory available..,0
2008-06-25 01:33:32.04 server Error: 17803, Severity: 20, State: 14,0
2008-06-25 01:33:32.04 server Insufficient memory available..,0
2008-06-25 01:33:37.02 spid1 LazyWriter: warning, no free buffers found.,0
2008-06-25 01:33:37.10 spid1 Buffer Distribution: Stolen=69570 Free=0 Procedures=864,0
Inram=622452 Dirty=82 Kept=0,0
I/O=0, Latched=374, Other=0,0
2008-06-25 01:33:37.10 spid1 Buffer Counts: Commited=693342 Target=693342 Hashed=622908,0
InternalReservation=1394 ExternalReservation=62837 Min Free=512,0
2008-06-25 01:33:37.10 spid1 Procedure Cache: TotalProcs=77 TotalPages=864 InUsePages=864,0
2008-06-25 01:33:37.10 spid1 Dynamic Memory Manager: Stolen=35974 OS Reserved=3312 ,0
OS Committed=3287,0
OS In Use=2897,0
Query Plan=1777 Optimizer=1854,0
General=29557,0
Utilities=1830 Connection=2346,0
2008-06-25 01:33:37.10 spid1 Global Memory Objects: Resource=1085 Locks=368 ,0
SQLCache=421 Replication=2,0
LockBytes=2 ServerGlobal=79,0
Xact=118,0
2008-06-25 01:33:37.10 spid1 Query Memory Manager: Grants=25 Waiting=0 Maximum=328809 Available=231519,0
2008-06-25 01:33:43.05 server Error: 17803, Severity: 20, State: 14,0
2008-06-25 01:33:43.05 server Insufficient memory available..,0
2008-06-25 01:33:47.02 spid1 LazyWriter: warning, no free buffers found.,0
2008-06-25 01:33:47.10 spid1 Buffer Distribution: Stolen=69570 Free=0 Procedures=864,0
Inram=622452 Dirty=82 Kept=0,0
I/O=0, Latched=374, Other=0,0
2008-06-25 01:33:47.10 spid1 Buffer Counts: Commited=693342 Target=693342 Hashed=622908,0
InternalReservation=1398 ExternalReservation=62837 Min Free=512,0
2008-06-25 01:33:47.10 spid1 Procedure Cache: TotalProcs=77 TotalPages=864 InUsePages=864,0
2008-06-25 01:33:47.10 spid1 Dynamic Memory Manager: Stolen=35974 OS Reserved=3312 ,0
OS Committed=3287,0
OS In Use=2897,0
Query Plan=1777 Optimizer=1854,0
General=29557,0
Utilities=1830 Connection=2346,0
2008-06-25 01:33:47.10 spid1 Global Memory Objects: Resource=1085 Locks=368 ,0
SQLCache=421 Replication=2,0
LockBytes=2 ServerGlobal=79,0
Xact=118,0
2008-06-25 01:33:47.10 spid1 Query Memory Manager: Grants=25 Waiting=0 Maximum=328809 Available=231519,0
2008-06-25 01:34:00.31 spid3 SQL Server is terminating due to 'stop' request from Service Control Manager.,0
SQL Service account has been assigned Lock pages in memory privilege.
Please help.
I posted this before but as noone replied and issue is still persisting I thought of posting it again.
Any help is greatly appreciated.
Manu
July 3, 2008 at 12:12 am
Ouch. You have some nasty memory issues.
What's SQL's max and min memory set to?
Is there anything else running on that box?
2008-06-25 01:34:00.31 spid3 SQL Server is terminating due to 'stop' request from Service Control Manager.,0
Hmm, that normally indicates that someone stopped the service. Check the system application log, see if you can find any events that may indicated that someone is stopping SQL. It may be the cluster service.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2008 at 3:31 am
My advice is report this to Microsoft PSS, they may be able to help even though you aer on an old OS.
The most likely cause of the problem is a memory leak. This could be in the base SQL Serve engine, but is more likely to be in an xp or user code. PSS should be able to help identify the cause. One the cause is known, you may have to change the way you use the affected component to avoid the problem recurring.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 7, 2008 at 9:27 am
I would first upgrade to SP4 since SP3 support lapsed in July of 2007. Also, I would also check my OS patch levels as well. MS PSS will then be able to support you.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
July 8, 2008 at 9:32 am
Thanks for replying.
Gail,
SQL Server is configured to consume as muchmemory as it can. No limit specified for max server memory. Its set to default. This is a dedicated sql server machine. Only three databases(out of ehich only 1 is accessed more frequently than others) are hosted on it.
I don't see any ID requesting to stop sql server service.
I am not able to understand why sql is getting restarted on its own when its failing on memory. My understanding says it should stop responding to user requests but shouldn't restart. Please correct me if I am wrong.
The same situation is there on another cluster on other (SQL Server SP4) machine too.
Please share your thoughts.
Manu
July 8, 2008 at 10:14 am
On 64-bit SQL Server you need to set a maximum memory limit. Otherwise SQL Server adds the page file size to the actual server memory and tries to use all of it. This can make the system slow or unstable.
You need to set a maximum memory limit to allow enough free space for the OS and any other tasks that are running alongside SQL Server (e.g. Anti-virus, SQL backups, etc, etc). On an 8GB box, somewhere between 5.5 GB and 6.5 GB is likely to be about right, but you need to look at what is running at your place.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 8, 2008 at 10:43 am
What I suspect is happening is that the cluster service notices that SQL is not responding (IsAlive Check) when SQL hits memory problems and forces a fail-over to the other cluster node.
I second Ed's comments on memory. You must set max memory on 64 bit.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2008 at 10:55 am
Thanks again guys. I will specify the max memory setting and will get back to you on this.
Gail,
I think you are right regarding service restart thought.
Thanks,
Manu
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply