Answered by:
Suspected Memory Leak - how to identify it in SQL 2005

Question
-
I have a SQL 2005 server which is not using AWE and has the default min/max memory settings of 0 and 2GB respectively. The Server will run for 1-2 weeks and then become totally unresponsive. The SQL Server error log has nasty messages in it like:
10/06/2009 00:17:11,Logon,Unknown,SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: 10.9.198.53]
10/06/2009 00:17:11,Logon,Unknown,Error: 17189<c/> Severity: 16<c/> State: 1.
and
10/06/2009 05:30:00,spid63,Unknown,There is insufficient system memory to run this query.
10/06/2009 05:30:00,spid63,Unknown,Error: 701<c/> Severity: 17<c/> State: 123.
I also noticed this error 1 day before the above messages.
10/05/2009 00:16:52,Server,Unknown,Resource Monitor (0x7ac) Worker 0x03D1C0E8 appears to be non-yielding on Node 0. Memory freed: 39400 KB. Approx CPU Used: kernel 15 ms<c/> user 0 ms<c/> Interval: 65000.
10/05/2009 00:16:52,Server,Unknown,External dump process return code 0x20000001.<nl/>External dump process returned no errors.
10/05/2009 00:16:29,Server,Unknown,Stack Signature for the dump is 0x00000379
10/05/2009 00:16:28,Server,Unknown,* Short Stack Dump
10/05/2009 00:16:28,Server,Unknown,* -------------------------------------------------------------------------------
10/05/2009 00:16:28,Server,Unknown,* *******************************************************************************
10/05/2009 00:16:28,Server,Unknown,*
10/05/2009 00:16:28,Server,Unknown,* Non-yielding Resource Monitor
10/05/2009 00:16:28,Server,Unknown,*
10/05/2009 00:16:28,Server,Unknown,* 10/05/09 00:16:28 spid 0
10/05/2009 00:16:28,Server,Unknown,* BEGIN STACK DUMP:
10/05/2009 00:16:28,Server,Unknown,*
10/05/2009 00:16:28,Server,Unknown,* *******************************************************************************
10/05/2009 00:16:28,Server,Unknown,***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0009.txt
10/05/2009 00:16:28,Server,Unknown,**Dump thread - spid = 0<c/> PSS = 0x00000000<c/> EC = 0x00000000
10/05/2009 00:16:26,Server,Unknown,Using 'dbghelp.dll' version '4.0.5'
I suspect a memory leak is at work here, but it is difficult to track down what resource is actually leaking.
I have since turned on SQL Profiling and a Perfmon log to find out more about what is happening in the lead up to the failure, but the failure repeats like clockwork.
Suggestions would be greatly appreciated.
ThxThursday, October 15, 2009 8:09 PM
Answers
-
Most suspected memory leaks are not memory leaks but just the default behaviour on installations left on default memory configuration settings.The default max server memory setting (of 2147483647) is in MB, so is a bit more than 2GB.I'd start narrowing down the issue by installing SP3 (if it's not already installed) as this resolved a few memory leaks and by looking at the Memory Bottlenecks section of the 2005 P&T guide.
ajmer dhariwal || eraofdata.com- Marked as answer by Xiao-Min Tan – MSFT Friday, October 23, 2009 8:26 AM
Thursday, October 15, 2009 8:42 PMAnswerer
All replies
-
Most suspected memory leaks are not memory leaks but just the default behaviour on installations left on default memory configuration settings.The default max server memory setting (of 2147483647) is in MB, so is a bit more than 2GB.I'd start narrowing down the issue by installing SP3 (if it's not already installed) as this resolved a few memory leaks and by looking at the Memory Bottlenecks section of the 2005 P&T guide.
ajmer dhariwal || eraofdata.com- Marked as answer by Xiao-Min Tan – MSFT Friday, October 23, 2009 8:26 AM
Thursday, October 15, 2009 8:42 PMAnswerer -
I have a SQL 2005 server which is not using AWE and has the default min/max memory settings of 0 and 2GB respectively. The Server will run for 1-2 weeks and then become totally unresponsive. The SQL Server error log has nasty messages in it like:
10/06/2009 00:17:11,Logon,Unknown,SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: 10.9.198.53]
10/06/2009 00:17:11,Logon,Unknown,Error: 17189<c/> Severity: 16<c/> State: 1.
Check and see if you have a .NET application that is connecting with a network packet size of 8KB which requires multi-page allocations from VAS. If you do, change the connection string to include (Packet Size=4096)
http://support.microsoft.com/kb/903002
select s.session_id, s.host_name, s.host_process_id, s.program_name, c.net_packet_size from sys.dm_exec_connections c join sys.dm_exec_sessions s on c.session_id = s.session_id where net_packet_size >= 8000
If you have rows over 8000 bytes you are running risk for VAS problems which is what your error message says. Do you use Linked Servers, Extended Stored Procedures, SQLCLR, a COM/OLE Automation routine, or XML prepared documents in your code? These are all VAS consumers. Also do you use stored procedures or do you have a non-parameterized adhoc workload? This will cause plan cache bloat and can lead to over commitment of the VAS.
Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!Friday, October 16, 2009 1:17 AM