CODEWITHSUNDEEP
sql-serverado.netdatabase-connectionconnection-poolingconnection-leaks
Learner
Learner

Reputation: 4751

SQL Server connection handling, connection pooling, lifetime and leaked connections

I am trying to identify SQL connection leaks in my application. After some operations, when my application is idle (user not doing any activity), I see 7 connections with my database in the result set returned by sp_who2. The status for all connections is Sleeping and Command value for all of them is AWAITING COMMAND.

I am using connection pooling but Connection Lifetime is not specified in the connection string. This means that it's default value 0 will be used if I am right. Connection Lifetime having value zero means SQL server should not close connection ever, right?

I keep my application idle for some time (15-20 minutes) and then I see that sp_who2 does NOT show any connection with my database. I am wondering why I get this result when Connection Lifetime is zero. Does SQL Server terminates unused connection after some time regardless of the Connection Lifetime value?

How can I identify which connection is open due to leakage and which is hanging around there due to connection pooling?

My application supports SQL Server 2008, 2014 and 2016. It's ASP.NEt application.

Upvotes: 0

Views: 1869

Answers (1)

SMor
SMor

Reputation: 2872

Connection Lifetime having value zero means SQL server should not close connection ever, right?

No. The rules used to purge the pool are not well documented AFAIK, but they can be closed by the pooler. Refer https://msdn.microsoft.com/en-us/library/8xx3tyca%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396.

I don't think there is a reliable/documented method to identify pooled connections from within the database engine.

Upvotes: 0

Related Questions