patterncsharpMinor
Trace unclosed connections
Viewed 0 times
connectionstraceunclosed
Problem
I get timeout errors every couple of days or so between IIS7 and an SQL 2008 Server. The error message I get is:
Sometimes this sorts itself out, other times I need to manually restart SQL Server. Is there any way I can trace down the cause of this error. I'm 90% sure it's somewhere without our ASP.NET webforms app where a connection is being opened and not closed, but I'm at a loss to pin down the cause.
System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.Sometimes this sorts itself out, other times I need to manually restart SQL Server. Is there any way I can trace down the cause of this error. I'm 90% sure it's somewhere without our ASP.NET webforms app where a connection is being opened and not closed, but I'm at a loss to pin down the cause.
Solution
You can query
From this, you can find out the last SQL executed
sys.dm_exec_sessions on the last_request_end_time column to find open yet "sleeping" connections. From this, you can find out the last SQL executed
SELECT session_id, TEXT
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS ST
WHERE c.session_id = Code Snippets
SELECT session_id, TEXT
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS ST
WHERE c.session_id = <suspicious one from sys.dm_exec_sessions>Context
StackExchange Database Administrators Q#11650, answer score: 4
Revisions (0)
No revisions yet.