July 2, 2009 at 6:56 am
This morning my SQL Server (2005) locked up and would not allow us to log in. We had to restart the server. Once I was able to get in I found very strange errors in my logs?? Can someone tell me what these are?
Date,Source,Severity,Message
07/02/2009 01:25:05,Server,Unknown,External dump process return code 0x20002001.
07/02/2009 01:24:54,Server,Unknown,Stack Signature for the dump is 0x0000000000000286
07/02/2009 01:24:54,Server,Unknown,* Short Stack Dump
07/02/2009 01:24:54,Server,Unknown,* -------------------------------------------------------------------------------
07/02/2009 01:24:54,Server,Unknown,* *******************************************************************************
07/02/2009 01:24:54,Server,Unknown,*
07/02/2009 01:24:54,Server,Unknown,* Non-yielding IOCP Listener
07/02/2009 01:24:54,Server,Unknown,*
07/02/2009 01:24:54,Server,Unknown,* 07/02/09 01:24:54 spid 0
07/02/2009 01:24:54,Server,Unknown,* BEGIN STACK DUMP:
07/02/2009 01:24:54,Server,Unknown,*
07/02/2009 01:24:54,Server,Unknown,* *******************************************************************************
07/02/2009 01:24:54,Server,Unknown,***Stack Dump being sent to D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0001.txt
07/02/2009 01:24:54,Server,Unknown,**Dump thread - spid = 0
07/02/2009 01:24:54,Server,Unknown,Using 'dbghelp.dll' version '4.0.5'
07/02/2009 01:01:10,spid2s,Unknown,MEMORYBROKER_FOR_RESERVE
07/02/2009 01:01:10,spid2s,Unknown,MEMORYBROKER_FOR_STEAL
07/02/2009 01:01:10,spid2s,Unknown,MEMORYBROKER_FOR_CACHE
07/02/2009 01:01:10,spid2s,Unknown,Big Gateway
07/02/2009 01:01:10,spid2s,Unknown,Medium Gateway
07/02/2009 01:01:10,spid2s,Unknown,Small Gateway
07/02/2009 01:01:10,spid2s,Unknown,Optimization Queue
07/02/2009 01:01:10,spid2s,Unknown,Small Query Memory Objects: Grants=0 Waiting=0 Maximum=12800 Available=12800 Limit=12800
07/02/2009 01:01:10,spid2s,Unknown,Query Memory Objects: Next Request=0 Waiting For=0 Cost=0 Timeout=0 Wait Time=0 Last Target=1050390
07/02/2009 01:01:10,spid2s,Unknown,Query Memory Objects: Grants=1 Waiting=0 Maximum=1008775 Available=724551 Limit=1037590
07/02/2009 01:01:10,spid2s,Unknown,Global Memory Objects:
07/02/2009 01:01:10,spid2s,Unknown,Process physical/virtual memory pressure: 0/0 System physical memory pressure: 0
07/02/2009 01:01:10,spid2s,Unknown,Procedure Cache: TotalProcs=33403 TotalPages=345078 InUsePages=1566
07/02/2009 01:01:10,spid2s,Unknown,Buffer Counts: Committed=1548288 Target=1567056 Hashed=1076098
07/02/2009 01:01:10,spid2s,Unknown,Buffer Distribution: Stolen=125145 Free=0 Cached=347045
07/02/2009 01:01:10,spid2s,Unknown,OBJECTSTORE_LOCK_MANAGER (Total)
07/02/2009 01:01:10,spid2s,Unknown,OBJECTSTORE_SERVICE_BROKER (Total)
07/02/2009 01:01:10,spid2s,Unknown,OBJECTSTORE_SNI_PACKET (Total)
07/02/2009 01:01:10,spid2s,Unknown,OBJECTSTORE_LBSS (Total)
07/02/2009 01:01:10,spid2s,Unknown,USERSTORE_SXC (Total)
07/02/2009 01:01:10,spid2s,Unknown,USERSTORE_OBJPERM (Total)
07/02/2009 01:01:10,spid2s,Unknown,USERSTORE_TOKENPERM (Total)
07/02/2009 01:01:10,spid2s,Unknown,USERSTORE_DBMETADATA (Total)
07/02/2009 01:01:10,spid2s,Unknown,USERSTORE_SCHEMAMGR (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_SYSTEMROWSET (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_EVENTS (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_BROKERTO (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_BROKERREADONLY (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_BROKERRSB (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_BROKERUSERCERTLOOKUP (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_BROKERDSH (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_BROKERKEK (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_BROKERTBLACS (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_STACKFRAMES (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_XMLDBATTRIBUTE (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_XMLDBELEMENT (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_XMLDBTYPE (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_VIEWDEFINITIONS (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_NOTIF (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_TEMPTABLES (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_XPROC (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_PHDR (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_SQLCP (Total)
07/02/2009 01:01:10,spid2s,Unknown,CACHESTORE_OBJCP (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_SOSNODE (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_HOST (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_SQLQERESERVATIONS (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_BHF (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_SQLXP (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_FULLTEXT (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_SNI (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_SQLHTTP (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_SQLSERVICEBROKER (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_SQLCLR (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_SQLCONNECTIONPOOL (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_SQLSTORENG (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_SQLUTILITIES (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_SQLOPTIMIZER (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_SQLQUERYEXEC (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_SQLBUFFERPOOL (Total)
07/02/2009 01:01:10,spid2s,Unknown,MEMORYCLERK_SQLGENERAL (Total)
07/02/2009 01:01:10,spid2s,Unknown,Memory node Id = 0
07/02/2009 01:01:10,spid2s,Unknown,Memory Manager
07/02/2009 01:01:10,spid2s,Unknown,LazyWriter: warning
July 2, 2009 at 7:25 am
I have 12 GB of memory and running service pack 2. Could this bad memory problem?
July 2, 2009 at 7:31 am
hi,
Make sure to set the limits on the amount of memory and worker threads that SQL Server uses. If you only have 12GB of memory, SQL should be limited to perhaps 10GB (read the specs for your SQL Server Edition). The OS needs to consume memory as well, and I have seem SQL crashed because it tries to consume all the memory to the point that there is nothing left for the input buffer (can't login).
It will take one run away query to eat all the memory, so check some of the procedures/jobs.
hope this helps.
July 2, 2009 at 7:49 am
Where can I set those limits? I looked at the jobs and we don't really have anything running at the time it crashed.
July 2, 2009 at 8:10 am
Using SSMS (SS management studio), right click the server node and select properties. Click memory and set the max server memory.
Then click Processor, and set the MAx worker threads.
Something must be eating the memory, so check any procedure calls/query.
I posted a note on the memory settings based on the SS edition. Look for post under my profile.
hope this helps.
July 2, 2009 at 8:15 am
You need to enable awe from sp_configuration and define the max server memory and min server memory, before going for this keep in mind you are also running operating system in the box so you need to reserve some memory for it as well. 😀
Nitin Gupta
Thanks & Regards,
Nitin Gupta
SQl Server (7.0,2000,2005) - Sr. DBA
HCL Technologies, India
July 2, 2009 at 8:59 am
When I looked at the server memory options this is what it's set too?
MIN is 0(mb)
MAX is set to 2147483647(mb)
other memory options
Index creation memory = 0
Min memory per query = 1024 (kb)
July 2, 2009 at 10:20 am
Molly Cary (7/2/2009)
When I looked at the server memory options this is what it's set too?MIN is 0(mb)
MAX is set to 2147483647(mb)
other memory options
Index creation memory = 0
Min memory per query = 1024 (kb)
It means SQL Server will take all the memory it can, potentially starving your OS. If you are only running SQL Server on this system and only one instance, I'd set the Max memory to 10240 MB.
Can't remember, is this an x64 system?
July 6, 2009 at 7:40 pm
Just in case anyone is in any doubt, the specific error "Non-Yielding IOCP Listener" is indeed directly related to an inappropriate setting for server max memory - though I have never seen it personally on a server dedicated to SQL Server.
A very detailed look at the issue can be found here
Regardless of whether this is a 32-bit or 64-bit server, whether Enterprise Edition or not, or any of the other myriad interesting settings...you should set max memory at least 4GB below physical and monitor it. When the server reaches steady-state (hours/days), you can adjust the max memory setting appropriately, always leaving at least 1GB 'free' to cope with sudden memory demands from the OS or other applications and services running on the server.
See this article for more information and links.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 9, 2009 at 12:25 am
There is FIX available for this issue check the following link:
http://support.microsoft.com/kb/941689
Cheers,
Got an idea..share it !!
DBA_Vishal
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply