Finding out where (in code) the connections to sql server are not getting closed

We had a problem where lots of connections were being created to the SQL Server and it just kept going up. The web application is just too big to manually check all the code pages to see where the connections were not getting closed properly.

(if you are using .net it’s better to use ‘USING’ so the connection is properly disposed)

I wanted to find out what query was run on the connections that got opened and didn’t get closed. That way I could check where in code the sql was getting called and make sure the connection was getting closed there.

For this we are going to use the dynamic management view dm_exec_connections and dynamic management function dm_exec_sql_text.

• dm_exec_connections returns information about the established connections to this sql server instance. The column that we are most interested in is the most_recent_sql_handle, as this would have an handle to the sql that was run last before the connection was abandoned.
• dm_exec_sql_text is a table valued function which takes a sql_handle(or plan_handle) parameter and returns the database id and text(this is null if the if the object is encrypted) among other things.

The following query will give you list of connections on which the read or write was done 5 mins ago along with the last sql that was run on it.

SELECT *
FROM sys.dm_exec_connections C
CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) T
where
last_read < DATEADD(minute, -5, getdate()) or last_write < DATEADD(minute, -5, getdate())

here is a image of running the above query on my sql server

Once you know what was run last on the sql server for a connection, you just need to check where in code it’s getting called and fix it.

Hope this little article was helpful.