How long does sqlserver keep connections active after they are closed ?

Question

Hi,

In a asp.net/Sqlserver project, we create connections using ado.net (sql authentication) and we see a behaviour where there are a lot of active connections in "sleeping","Awaiting command" status

The code does the following - Get a connection from common function, update db, Commit transaction, close & dispose transaction, close connection.

1) In sqlserver 2008 when our program runs for sometime (it updates the db every few secs), the number of active connections increases dramatically and sqlserver starts refusing new connections (as the default connections is 100) 2) In sqlserver 2005, we see that the connections are getting reused and work fine. Our max connections does not go above 15-20.

We found an issue from MSFT on 2008 and conveyed to the client. https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=383517 - Talks about 2008 not releasing closed connections immediately.

In the client place, we see the same issue in sql2005 too.

My question, is when the .net program calls close() on a connection, how long does sqlserver keeps it active ?

Thanks a lot for any hints

Regards Anand

Answer 1

Connections are going to the pool. If they are not reused from there, and the number of connections increases, you certainly did not clean them up properly. Make use of using blocks for any disposable type (also transactions, commands and whatever).

To clear the connection pool you can call this static method:

SqlConnection.ClearAllPools();

This should remove all connections and are not used in the pool. The others are still in use.