Does missing logout event in SQL Server trace mean connection leak? (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?
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