MSSQL

Connection Leak in MSSQL

The error occurs as a result of connection leaks in the application.

Photo by panumas nikhomkhai from Pexels

Any applications getting connection timeouts to MS-SQL Server will see the following error.

Message=Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and the max pool size was reached.

Most of the time, this failure happens due to connection leaks in the application. Run the following query to check the list of connection requests in the MSSQL Server:

select count(1) conection_count, program_name, host_name, status, host_process_id from sys.dm_exec_sessions where is_user_process = 1 group by program_name, host_name, status, host_process_id order by 1 desc

The connection requests are of two categories.

• running
• sleeping

The above query returns a list of connections, i.e., both running and sleeping.

Currently, in the above output, I don’t have any sleeping thread.

But by default, ADO.Net in C# application sets a max pool size to 100 connections per “host_process_id.” So if a process is holding up more than 100 sleeping connections, there is a possibility it might leak.

Thank you for reading, and I hope you liked the article. Please provide your feedback in the comment section. Don’t forget to clap..!!

Follow me on

C# Publication, LinkedIn, Instagram, Twitter, Dev.to, Pinterest, Substack, Wix.