Call SqlConnection.ClearAllPools () in Application_Start & Application_End?

Question

We are trying to diagnose a problem that occurred in our production environment last week. In short, the database connection pool seemed to be filled with active connections from our ASP.NET 3.5 application, which would not be cleared even after restarting the application pool and IIS.

The senior database administrator said that since network connections occur at the operating system level, the utilization of the application and IIS did not interfere with the actual network connections, so SQL Server left the database connections to continue working, and our application is still unable to access the database.

In searching for ways to force the pool of connections to the database reset, I found the static method SqlConnection.ClearAllPools() with documentation explaining what it does, but almost nothing explains when to call it. It seems to call it at the beginning of Application_Start , and the end of Application_End in my global.asax.cs file is a good security measure to protect the application from poisoned connection pools, although this, of course, will lead to a performance hit on / off.

Is this what I described as good practice? Is there a better one? The goal is to allow a simple restart of the application to reset the pool of connected applications without rebooting the OS or SQL Server service, which will affect many other applications.

Any guidance is greatly appreciated.

Answer 1

When a process dies, the entire network connection always, always, always closes immediately. This is at the TCP level. It has nothing to do with ADO.NET and is suitable for all applications. Kill the browser and all downloads will stop. Kill the FTP client and all connections will be closed immediately.

In addition, a pool of connections for each process. Therefore, cleaning up at application startup is useless because the pool is empty. Clearing it on shutdown is not required because all connections will be (gracefully) turned off at any time.

Your application may not return a connection to the pool. You must dispose of all compounds after use in all cases. If you do not, dangling connections will accumulate for an indefinite period of time.

Clearing the pool does not release dangling compounds because they are apparently used. How can ADO.NET say that you will never use them again? He can not.

Take a look at sys.dm_exec_connections to find out who supports the connections. You can increase the size of the ADO.NET pool as a stop measure. SQL Server can accept more than 30 thousand connections per instance. Usually you never saturate it.