(originally asked this question on StackOverflow was told that this community is a right one).

I am investigating possibility of unreleased SQL Server database connections. I ran SQL Server Profiler and collected event traces. Typically after an executed SQL command, I see "Audit Logout" event and often exec sp_reset_connection that indicates that connection is reused for a new command.

However, I track some SPIDs and see that after some SQL command (e.g. INSERT), there are no more events for the given SPID. Does this mean that connection is not released, stays open and can not be reused?

up vote 2 down vote accepted

Great question! To find out if the connection is still open right now, look in sys.sysprocesses:

SELECT * FROM sys.sysprocesses;

Generally, as long as they're not doing anything, open connections aren't a problem for SQL Server - it can handle tens of thousands. Connection pooling - keeping a connection open and ready for reuse - is fine, and doesn't consume that many resources on a SQL Server.

I'm generally more worried about sleeping sessions - someone who started a request minutes ago and didn't finish - and you can find those with Adam Machanic's free sp_WhoIsActive:

sp_WhoIsActive @ShowSleepingSpids = 1
  • It may be that some apps are using persistent connections instead of closing them and letting connection pooling handle reuse. This doesn't leak connections but will keep connections open indefinitely that are used only when needed, where it could be a very long time (hours or days) before the next query. – Dan Guzman Jun 12 at 11:26

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.