September 29, 2010 at 11:21 am
We are running SQL Server 2005 SP3 and having issues where our database CPU spikes and it won't accept new connections. This happened on Monday, and it happened again today. Today the SQLDumper tried to start, which spikes the CPU. The problem with this is that we can't let it dump everything because the box has 32 GB of RAM and it would take forever, during this time no one can access the system. Here is what showed up in the event log.
EventType sql90exception64, P1 sqlservr.exe, P2 2005.90.4053.0, P3 000000004a1cb91f, P4 sqlservr.exe, P5 2005.90.4053.0, P6 000000004a1cb91f, P7 0, P8 0000000000f77b13, P9 00000000000002c1, P10 NIL.
We had to kill the process to get it to come back. We've tried simply restarting the SQL Server to give it a clean shutdown, but don't have much luck. We run 5 different databases on this box. The main one that everyone uses most is 153 GB in size. The other 4 databases are accessed through synonyms and are all on the same server. We have a logs DB which is approx 57GB in size, reports which is 8GB, Temp which is around 1-2 GB and then the Documents DB which is less than a gig in size. I guess what we're having problems with is determining the source of the problem.
We seem to be having a large amount of records being written to the databases at about the times of the failures, but this happens at other times and it doesn't cause issues.
Any insight is appreciated,
Adam
September 30, 2010 at 4:08 am
Is it a 32Bits or 64Bits?
I guess 64Bits, but just wanna make sure 🙂
Do you have a lot of connections (thousands)?
Did you check the sql error log?
September 30, 2010 at 5:43 am
Yes it is 64 bit.
We generally have between 100-200 connections, sometimes in the 200s when in high use. I looked at the logs but didn't see anything that seemed very helpful. I'll double check them though. Our tempdb grew from nothing to over 60gb within 5 hous of resetting it. I'm not sure if that's related but it is something we are looking into.
September 30, 2010 at 8:52 am
Here is what is in the error log a little before and during the error. I have no idea how to decipher it and it doesn't really point me in any direction. If I can provide more info, let me know.
2010-09-29 12:12:10.16 spid148 AppDomain 61 (XXX.dbo[runtime].62) created.
2010-09-29 12:12:23.25 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:12:23.25 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:13:20.62 spid1s AppDomain 61 (XXX.dbo[runtime].62) is marked for unload due to memory pressure.
2010-09-29 12:13:20.63 spid1s AppDomain 61 (XXX.dbo[runtime].62) unloaded.
2010-09-29 12:13:24.24 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:13:24.24 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:14:24.29 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:14:24.29 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:15:24.79 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:15:24.79 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:15:30.48 spid148 AppDomain 62 (XXX.dbo[runtime].63) created.
2010-09-29 12:16:20.86 spid1s AppDomain 62 (XXX.dbo[runtime].63) is marked for unload due to memory pressure.
2010-09-29 12:16:20.86 spid1s AppDomain 62 (XXX.dbo[runtime].63) unloaded.
2010-09-29 12:16:25.25 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:16:25.25 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:16:46.29 spid154 AppDomain 63 (XXX.dbo[runtime].64) created.
2010-09-29 12:17:25.28 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:17:25.28 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:18:25.53 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:18:25.53 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:19:26.15 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:19:26.15 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:20:26.77 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:20:26.77 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:21:27.19 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:21:27.19 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:22:27.54 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:22:27.54 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:23:27.99 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:23:27.99 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:24:28.27 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:24:28.27 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:25:28.70 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:25:28.70 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:26:29.10 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:26:29.10 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:27:29.31 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:27:29.31 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:28:29.81 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:28:29.81 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:29:30.24 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:29:30.24 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:30:30.90 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:30:30.90 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:31:32.30 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:31:32.30 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:31:37.58 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:31:37.58 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:31:42.81 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:31:42.81 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:31:52.95 Logon Error: 18456, Severity: 14, State: 16.
2010-09-29 12:31:52.95 Logon Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.71.36.30]
2010-09-29 12:35:09.38 Server Using 'dbghelp.dll' version '4.0.5'
2010-09-29 12:35:09.60 Server **Dump thread - spid = 0, PSS = 0x0000000000000000, EC = 0x0000000000000000
2010-09-29 12:35:09.61 Server ***Stack Dump being sent to D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0006.txt
2010-09-29 12:35:09.61 Server * *******************************************************************************
2010-09-29 12:35:09.61 Server *
2010-09-29 12:35:09.61 Server * BEGIN STACK DUMP:
2010-09-29 12:35:09.61 Server * 09/29/10 12:35:09 spid 0
2010-09-29 12:35:09.61 Server *
2010-09-29 12:35:09.61 Server * Non-yielding IOCP Listener
2010-09-29 12:35:09.62 Server *
2010-09-29 12:35:09.62 Server * *******************************************************************************
2010-09-29 12:35:09.62 Server * -------------------------------------------------------------------------------
2010-09-29 12:35:09.62 Server * Short Stack Dump
2010-09-29 12:35:09.80 Server Stack Signature for the dump is 0x00000000000002C1
2010-09-29 12:35:19.30 spid2s LazyWriter: warning, no free buffers found.
2010-09-29 12:35:22.04 spid2s
Memory Manager
VM Reserved = 40106280 KB
VM Committed = 33854372 KB
AWE Allocated = 0 KB
Reserved Memory = 1024 KB
Reserved Memory In Use = 0 KB
2010-09-29 12:35:22.04 spid2s
Memory node Id = 0
VM Reserved = 40100520 KB
VM Committed = 33848700 KB
AWE Allocated = 0 KB
SinglePage Allocator = 4971328 KB
MultiPage Allocator = 44768 KB
2010-09-29 12:35:22.04 spid2s
MEMORYCLERK_SQLGENERAL (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 146912 KB
MultiPage Allocator = 4648 KB
2010-09-29 12:35:22.05 spid2s
MEMORYCLERK_SQLBUFFERPOOL (Total)
VM Reserved = 33603584 KB
VM Committed = 33587200 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 0 KB
MultiPage Allocator = 24 KB
2010-09-29 12:35:22.05 spid2s
MEMORYCLERK_SQLQUERYEXEC (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8088 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.05 spid2s
MEMORYCLERK_SQLOPTIMIZER (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 1384 KB
MultiPage Allocator = 112 KB
2010-09-29 12:35:22.05 spid2s
MEMORYCLERK_SQLUTILITIES (Total)
VM Reserved = 360 KB
VM Committed = 360 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 312 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.05 spid2s
MEMORYCLERK_SQLSTORENG (Total)
VM Reserved = 4672 KB
VM Committed = 4672 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 3224 KB
MultiPage Allocator = 832 KB
2010-09-29 12:35:22.05 spid2s
MEMORYCLERK_SQLCONNECTIONPOOL (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 2080 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.05 spid2s
MEMORYCLERK_SQLCLR (Total)
VM Reserved = 6313856 KB
VM Committed = 79444 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 1448 KB
MultiPage Allocator = 21984 KB
2010-09-29 12:35:22.05 spid2s
MEMORYCLERK_SQLSERVICEBROKER (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 104 KB
MultiPage Allocator = 304 KB
2010-09-29 12:35:22.98 spid2s
MEMORYCLERK_SQLHTTP (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
MEMORYCLERK_SNI (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 280 KB
MultiPage Allocator = 16 KB
2010-09-29 12:35:22.98 spid2s
MEMORYCLERK_FULLTEXT (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 16 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
MEMORYCLERK_SQLXP (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 16 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
MEMORYCLERK_BHF (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 192 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
MEMORYCLERK_SQLQERESERVATIONS (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 797176 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
MEMORYCLERK_HOST (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 88 KB
MultiPage Allocator = 16 KB
2010-09-29 12:35:22.98 spid2s
MEMORYCLERK_SOSNODE (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 95296 KB
MultiPage Allocator = 12912 KB
2010-09-29 12:35:22.98 spid2s
MEMORYCLERK_FULLTEXT_SHMEM (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 64 KB
SM Committed = 64 KB
SinglePage Allocator = 0 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 48 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_OBJCP (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 77344 KB
MultiPage Allocator = 928 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_SQLCP (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 4594768 KB
MultiPage Allocator = 864 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_PHDR (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8904 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_XPROC (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 24 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_TEMPTABLES (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 16 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_NOTIF (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 16 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_VIEWDEFINITIONS (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 16 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_XMLDBTYPE (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_XMLDBELEMENT (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_XMLDBATTRIBUTE (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_STACKFRAMES (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 0 KB
MultiPage Allocator = 8 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_BROKERTBLACS (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 128 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_BROKERKEK (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_BROKERDSH (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_BROKERUSERCERTLOOKUP (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_BROKERRSB (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_BROKERREADONLY (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 32 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_BROKERTO (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_EVENTS (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 16 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_CLRPROC (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
CACHESTORE_SYSTEMROWSET (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 1144 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
USERSTORE_SCHEMAMGR (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 4152 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
USERSTORE_DBMETADATA (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 2760 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
USERSTORE_TOKENPERM (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 4680 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
USERSTORE_OBJPERM (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 1528 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
USERSTORE_SXC (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 648 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.98 spid2s
OBJECTSTORE_LBSS (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 128 KB
MultiPage Allocator = 1888 KB
2010-09-29 12:35:22.98 spid2s
OBJECTSTORE_SNI_PACKET (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 10760 KB
MultiPage Allocator = 48 KB
2010-09-29 12:35:22.98 spid2s
OBJECTSTORE_SERVICE_BROKER (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 272 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.99 spid2s
OBJECTSTORE_LOCK_MANAGER (Total)
VM Reserved = 131072 KB
VM Committed = 131072 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 4384 KB
MultiPage Allocator = 0 KB
2010-09-29 12:35:22.99 spid2s Buffer Distribution: Stolen=38052 Free=0 Cached=588107
Database (clean)=3485201 Database (dirty)=17400
I/O=0, Latched=8
2010-09-29 12:35:22.99 spid2s Buffer Counts: Committed=4128768 Target=4194174 Hashed=3502609
Internal Reservation=838834 External Reservation=94905
Stolen Potential=3263401
Min Free=256 Visible=4194174
Available Paging File=31043682304
2010-09-29 12:35:22.99 spid2s Procedure Cache: TotalProcs=5747 TotalPages=585354 InUsePages=1155
2010-09-29 12:35:22.99 spid2s Process physical/virtual memory pressure: 0/0 System physical memory pressure: 0
2010-09-29 12:35:23.99 spid2s Global Memory Objects:
Resource=470
Locks=551
SE Schema Mgr=517
SQLCache=18102
Replication=2
ServerGlobal=49
XPGlobal=2
Xact=100
SETLS=8
DatasetMemObjs=16
SubpDescPmos=8
SortTables=2
2010-09-29 12:35:23.99 spid2s Query Memory Objects: Grants=5 Waiting=0 Maximum=3138955 Available=3039308 Limit=3138955
2010-09-29 12:35:23.99 spid2s Query Memory Objects: Next Request=0 Waiting For=0 Cost=0 Timeout=0 Wait Time=0 Last Target=3151755
2010-09-29 12:35:23.99 spid2s Small Query Memory Objects: Grants=0 Waiting=0 Maximum=12800 Available=12800 Limit=12800
2010-09-29 12:35:23.99 spid2s
Optimization Queue
Overall Memory = 27540504576 Target Memory = 21394358272 Last Notification = GROW Timeout = 6 Early Termination Factor = 5
2010-09-29 12:35:23.99 spid2s
Small Gateway
Configured Units = 32 Available Units = 31 Acquires = 1
Waiters = 0 Threshold Factor = 380000 Threshold = 380000
2010-09-29 12:35:23.99 spid2s
Medium Gateway
Configured Units = 8 Available Units = 8 Acquires = 0
Waiters = 0 Threshold Factor = 12 Threshold = 1782863189
2010-09-29 12:35:23.99 spid2s
Big Gateway
Configured Units = 1 Available Units = 1 Acquires = 0
Waiters = 0 Threshold Factor = 8 Threshold = -1
2010-09-29 12:35:23.99 spid2s
MEMORYBROKER_FOR_CACHE
Allocations = 588111
Rate = 0
Target Allocations = 3166422
Future Allocations = 0
Last Notification = GROW
2010-09-29 12:35:23.99 spid2s
MEMORYBROKER_FOR_STEAL
Allocations = 33305
Rate = 0
Target Allocations = 2611616
Future Allocations = 0
Last Notification = GROW
2010-09-29 12:35:23.99 spid2s
MEMORYBROKER_FOR_RESERVE
Allocations = 99647
Rate = 0
Target Allocations = 3361878
Future Allocations = 784738
Last Notification = GROW
2010-09-29 12:35:36.02 Server External dump process return code 0x20002001.
The error information has been submitted to Watson error reporting.
September 30, 2010 at 9:03 am
How big is the partition that tempdb is on? What does your sysprocesses table look like when the cpu starts spiking ( as in, do you see one spid absorbing alot of cpu)?
You say there isn't anything of consequence in the sql server logs - is there ANY kind of error around the time frame sql server freaks out?
September 30, 2010 at 10:50 am
The tempdb is on the same partition as the other databases. It has about 70GB of free space when the service starts up. Tempdb ends up consuming much of that eventually. I haven't had a chance to look at the sysprocesses because once it starts spiking, we can't get access to the server. We've recently learned about the dedicated admin connection, and if it happens again, we plan on using it to see what's going on.
The log information I posted above was from the error log at the time that it spiked yesterday. The dump started at 12:35, and the error log info I posted is from about 10-11 minutes before that up until the dump. If you're asking what is in the ldf log file, I'm not sure how I can view that information, but would love to learn how.
October 1, 2010 at 1:13 am
I'm definately not an expert on reading stack dump, but it looks like your server ran out of memory.
Not enough memory can keep the server from accepting new connections of course and in this case it looks like it also kept the lazy writer from getting a new buffer (hence the crash dump).
Now you need to find out if the lack of memory comes from sql server itself or if it's from external memory pressure (another application taking all the available memory).
That's not something easy to find out on a post mortem though, but you could have a look in the application event log and see if other applications got a problem as well.
October 1, 2010 at 6:38 am
Unfortunately, I'm not sure it was physical memory that was the problem. The box has 32GB of RAM and at the time the issue arose, SQL Server was only using 24GB, and it had plenty of free space. I supposed there is a possibility that we have a bad memory module and that caused an issue. Unfortunately, we'll have to take the box down to run any of those diagnostics.
October 1, 2010 at 9:05 am
You might want to troubleshoot by minimizing the number of accessible databases. If tempdb is filling up all available storage, that's a huge clue as to what's causing the connection problems.
You said your main DB is ~150GB? You may have a temporary table that is being loaded with millions of records causing your physical storage problem.
October 13, 2010 at 2:05 pm
It failed again yesterday late afternoon. Processor spiked and it was doing a dump. No incoming connections were allowed. We were hoping we could use the dedicated admin connection, but even that would time out when trying to connect to it, so no luck there. TempDB was of course reset when we had to kill the process. Right now it's not very big, so it didn't grow wildly like last time. Below are the current statistics for TempDB.
usr_obj_kb
384
internal_obj_kb
8256
version_store_kb
1344
freespace_kb
2302272
mixedextent_kb
3648
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply