September 22, 2008 at 2:33 am
Morning all,
Had an issue over the weekend which stopped everything working, apps unable to connect to sql due to low memory. Ive been through the logs and here is the output after and including the page file allocation error. Was wondering if anyone could help me interpret the output?! Many thanks if you can! (The issue has been resolved now by restarting the sql service)
09/20/2008 21:10:25,Logon,Unknown,Error: 18456 State: 16.
09/20/2008 21:10:25,spid95,Unknown,There is insufficient system memory to run this query.
09/20/2008 21:10:25,spid95,Unknown,Error: 701 State: 96.
09/20/2008 21:10:25,spid95,Unknown,MEMORYBROKER_FOR_RESERVE Last Notification = STABLE
09/20/2008 21:10:25,spid95,Unknown,MEMORYBROKER_FOR_STEAL Last Notification = STABLE
09/20/2008 21:10:25,spid95,Unknown,MEMORYBROKER_FOR_CACHE Last Notification = SHRINK
09/20/2008 21:10:25,spid95,Unknown,Big Gateway Waiters = 0 Threshold Factor = 8 Threshold = -1
09/20/2008 21:10:25,spid95,Unknown,Medium Gateway Waiters = 0 Threshold Factor = 12 Threshold = -1
09/20/2008 21:10:25,spid95,Unknown,Small Gateway Waiters = 0 Threshold Factor = 250000 Threshold = 250000
09/20/2008 21:10:25,spid95,Unknown,Optimization Queue Overall Memory = 1095966720 Target Memory = 68493312 Last Notification = STABLE Timeout = 6 Early Termination Factor = 5
09/20/2008 21:10:25,spid95,Unknown,Small Query Memory Objects: Grants=0 Waiting=0 Maximum=640 Available=640 Limit=640
09/20/2008 21:10:25,spid95,Unknown,Query Memory Objects: Next Request=0 Waiting For=0 Cost=0 Timeout=0 Wait Time=0 Last Target=7830
09/20/2008 21:10:25,spid95,Unknown,Query Memory Objects: Grants=1 Waiting=0 Maximum=7190 Available=6418 Limit=7190
09/20/2008 21:10:25,spid95,Unknown,Global Memory Objects: SortTables=2
09/20/2008 21:10:25,spid95,Unknown,Process physical/virtual memory pressure: 1/0 System physical memory pressure: 0
09/20/2008 21:10:25,spid95,Unknown,Procedure Cache: TotalProcs=1 TotalPages=152888 InUsePages=29
09/20/2008 21:10:25,spid95,Unknown,Buffer Counts: Committed=874496 Target=2621440 Hashed=716206 Available Paging File=28209238016
09/20/2008 21:10:25,spid51,Unknown,There is insufficient system memory to run this query.
09/20/2008 21:10:25,spid51,Unknown,Error: 701 State: 193.
09/20/2008 21:10:25,spid95,Unknown,Buffer Distribution: Stolen=3183 Free=413 Cached=154694 Latched=1
09/20/2008 21:10:25,spid95,Unknown,OBJECTSTORE_LOCK_MANAGER (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,OBJECTSTORE_SERVICE_BROKER (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,OBJECTSTORE_SNI_PACKET (Total) MultiPage Allocator = 48 KB
09/20/2008 21:10:25,spid95,Unknown,OBJECTSTORE_LBSS (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,USERSTORE_SXC (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,USERSTORE_OBJPERM (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,USERSTORE_TOKENPERM (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,USERSTORE_DBMETADATA (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,USERSTORE_SCHEMAMGR (Total) MultiPage Allocator = 16 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_SYSTEMROWSET (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_EVENTS (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_BROKERTO (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_BROKERREADONLY (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_BROKERRSB (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_BROKERUSERCERTLOOKUP (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_BROKERDSH (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_BROKERKEK (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_BROKERTBLACS (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_STACKFRAMES (Total) MultiPage Allocator = 8 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_XMLDBATTRIBUTE (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_XMLDBELEMENT (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_XMLDBTYPE (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_VIEWDEFINITIONS (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_NOTIF (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_TEMPTABLES (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_XPROC (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_PHDR (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_SQLCP (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,CACHESTORE_OBJCP (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_SOSNODE (Total) MultiPage Allocator = 7760 KB
09/20/2008 21:10:25,spid51,Unknown,There is insufficient system memory to run this query.
09/20/2008 21:10:25,spid51,Unknown,Error: 701 State: 193.
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_HOST (Total) MultiPage Allocator = 16 KB
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_SQLQERESERVATIONS (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_BHF (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_SQLXP (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_FULLTEXT (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_SNI (Total) MultiPage Allocator = 16 KB
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_SQLHTTP (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_SQLSERVICEBROKER (Total) MultiPage Allocator = 192 KB
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_SQLCLR (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_SQLCONNECTIONPOOL (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_SQLSTORENG (Total) MultiPage Allocator = 1056 KB
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_SQLUTILITIES (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_SQLOPTIMIZER (Total) MultiPage Allocator = 72 KB
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_SQLQUERYEXEC (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_SQLBUFFERPOOL (Total) MultiPage Allocator = 7080 KB
09/20/2008 21:10:25,spid95,Unknown,MEMORYCLERK_SQLGENERAL (Total) MultiPage Allocator = 2192 KB
09/20/2008 21:10:25,spid95,Unknown,Memory node Id = 0 MultiPage Allocator = 18576 KB
09/20/2008 21:10:25,spid95,Unknown,Memory Manager Reserved Memory In Use = 0 KB
09/20/2008 21:10:25,spid84,Unknown,There is insufficient system memory to run this query.
09/20/2008 21:10:25,spid84,Unknown,Error: 701 State: 193.
09/20/2008 21:10:25,spid84,Unknown,MEMORYBROKER_FOR_RESERVE Last Notification = STABLE
09/20/2008 21:10:25,spid84,Unknown,MEMORYBROKER_FOR_STEAL Last Notification = STABLE
09/20/2008 21:10:25,spid84,Unknown,MEMORYBROKER_FOR_CACHE Last Notification = SHRINK
09/20/2008 21:10:25,spid84,Unknown,Big Gateway Waiters = 0 Threshold Factor = 8 Threshold = -1
09/20/2008 21:10:25,spid84,Unknown,Medium Gateway Waiters = 0 Threshold Factor = 12 Threshold = -1
09/20/2008 21:10:25,spid84,Unknown,Small Gateway Waiters = 0 Threshold Factor = 250000 Threshold = 250000
09/20/2008 21:10:25,spid84,Unknown,Optimization Queue Overall Memory = 1095966720 Target Memory = 68493312 Last Notification = STABLE Timeout = 6 Early Termination Factor = 5
09/20/2008 21:10:25,spid84,Unknown,Small Query Memory Objects: Grants=0 Waiting=0 Maximum=640 Available=640 Limit=640
09/20/2008 21:10:25,spid84,Unknown,Query Memory Objects: Next Request=0 Waiting For=0 Cost=0 Timeout=0 Wait Time=0 Last Target=7830
09/20/2008 21:10:25,spid84,Unknown,Query Memory Objects: Grants=1 Waiting=0 Maximum=7190 Available=6418 Limit=7190
09/20/2008 21:10:25,spid84,Unknown,Global Memory Objects: SortTables=2
09/20/2008 21:10:25,spid84,Unknown,Process physical/virtual memory pressure: 1/0 System physical memory pressure: 0
09/20/2008 21:10:25,spid84,Unknown,Procedure Cache: TotalProcs=1 TotalPages=152888 InUsePages=29
09/20/2008 21:10:25,spid84,Unknown,Buffer Counts: Committed=874496 Target=2621440 Hashed=716206 Available Paging File=28209233920
09/20/2008 21:10:25,spid84,Unknown,Buffer Distribution: Stolen=3181 Free=415 Cached=154694 Latched=1
09/20/2008 21:10:25,spid84,Unknown,OBJECTSTORE_LOCK_MANAGER (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,OBJECTSTORE_SERVICE_BROKER (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,OBJECTSTORE_SNI_PACKET (Total) MultiPage Allocator = 48 KB
09/20/2008 21:10:25,spid84,Unknown,OBJECTSTORE_LBSS (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,USERSTORE_SXC (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,USERSTORE_OBJPERM (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,USERSTORE_TOKENPERM (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,USERSTORE_DBMETADATA (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,USERSTORE_SCHEMAMGR (Total) MultiPage Allocator = 16 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_SYSTEMROWSET (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_EVENTS (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_BROKERTO (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_BROKERREADONLY (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_BROKERRSB (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_BROKERUSERCERTLOOKUP (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_BROKERDSH (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_BROKERKEK (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_BROKERTBLACS (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_STACKFRAMES (Total) MultiPage Allocator = 8 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_XMLDBATTRIBUTE (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_XMLDBELEMENT (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_XMLDBTYPE (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_VIEWDEFINITIONS (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_NOTIF (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_TEMPTABLES (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_XPROC (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_PHDR (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_SQLCP (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,CACHESTORE_OBJCP (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_SOSNODE (Total) MultiPage Allocator = 7744 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_HOST (Total) MultiPage Allocator = 16 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_SQLQERESERVATIONS (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_BHF (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_SQLXP (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_FULLTEXT (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_SNI (Total) MultiPage Allocator = 16 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_SQLHTTP (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_SQLSERVICEBROKER (Total) MultiPage Allocator = 192 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_SQLCLR (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_SQLCONNECTIONPOOL (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_SQLSTORENG (Total) MultiPage Allocator = 1056 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_SQLUTILITIES (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_SQLOPTIMIZER (Total) MultiPage Allocator = 72 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_SQLQUERYEXEC (Total) MultiPage Allocator = 0 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_SQLBUFFERPOOL (Total) MultiPage Allocator = 7080 KB
09/20/2008 21:10:25,spid84,Unknown,MEMORYCLERK_SQLGENERAL (Total) MultiPage Allocator = 2192 KB
09/20/2008 21:10:25,spid84,Unknown,Memory node Id = 0 MultiPage Allocator = 18576 KB
09/20/2008 21:10:25,spid84,Unknown,Memory Manager Reserved Memory In Use = 0 KB
09/20/2008 21:10:25,spid51,Unknown,There is insufficient system memory to run this query.
09/20/2008 21:10:25,spid51,Unknown,Error: 701 State: 193.
09/20/2008 21:10:25,spid58,Unknown,There is insufficient system memory to run this query.
09/20/2008 21:10:25,spid58,Unknown,Error: 701 State: 193.
09/20/2008 21:10:25,spid95,Unknown,Failed allocate pages: FAIL_PAGE_ALLOCATION 1
September 22, 2008 at 7:51 am
Hi Magic
Have u figured out which Query was causing this problem, i mean was it for a Query or for all the Quries from the applicaiotion.
cheers
🙂
September 22, 2008 at 8:23 am
it was everything, even trying to connect through SSMS failed. 10+ apps failed with the same messages. Backups failed overnight because they couldnt connect as well.
March 7, 2015 at 5:27 pm
Did you try reducing the max server memory on the SQL Server intance, leaving more memory to the OS?
This solved in one of my instances.
March 7, 2015 at 8:38 pm
SQLEnthusiastic (3/7/2015)
Did you try reducing the max server memory on the SQL Server intance, leaving more memory to the OS?This solved in one of my instances.
Good tip. I hope they're still listening after 7 years, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 8, 2015 at 3:08 am
Always listening Jeff 😛 moved on from that business 6 years ago though 🙂
March 8, 2015 at 6:21 pm
I really didn't see the date "TenCenturies" posted 🙁
But, could help the future visitors though.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply