3

(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?

2

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 '18 at 11:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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