May 6, 2012 at 6:33 am
Hi All,
Last night I have noticed the following errors around 2:00AM.
There are 10 of these errors:
The client was unable to reuse a session with SPID, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message
followed by:
Resource Monitor (0x830) Worker 0x000000008004C1A0 appears to be non-yielding on Node 0. Memory freed: 19728 KB. Approx CPU Used: kernel 62 ms, user 0 ms, Interval: 60091.
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 12175692, committed (KB): 27128316, memory utilization: 44%.
Currently SQLServer service account is part of lock pages in memory local policy, and max mem setting is 25.6 GB. This is a sql server 2008 R2 SE on Windows Server 2008 R2 SE server with 32GB of memory
The problem is we got high alerts stating DB Server is down and all the client connections were dropped.
Need your inputs friends...Thanks a bunch in advance.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
May 6, 2012 at 6:42 am
Sapen (5/6/2012)
Last night I have noticed the following errors around 2:00AM.There are 10 of these errors:
The client was unable to reuse a session with SPID, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message
followed by:
Resource Monitor (0x830) Worker 0x000000008004C1A0 appears to be non-yielding on Node 0. Memory freed: 19728 KB. Approx CPU Used: kernel 62 ms, user 0 ms, Interval: 60091.
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 12175692, committed (KB): 27128316, memory utilization: 44%.
Currently SQLServer service account is part of lock pages in memory local policy, and max mem setting is 25.6 GB. This is a sql server 2008 R2 SE on Windows Server 2008 R2 SE server with 32GB of memory
Link next may be a good starting point for troubleshooting http://blogs.msdn.com/b/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 6, 2012 at 6:57 am
Thanks Paul. We dont have any applications on the Production Database Server except for SQL server. What was happening was some of the backup files were being moved to an external hard drive around the same time and this has caused high IO.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
May 6, 2012 at 9:16 am
Sapen (5/6/2012)
What was happening was some of the backup files were being moved to an external hard drive around the same time and this has caused high IO.
File copies require memory, like any other operation. That file copy would be the thing that triggered a low memory warning and made the OS page SQL Server out.
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
May 6, 2012 at 10:29 am
OK..Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply