DEV Community

loading...
Cover image for Connection Leak in MSSQL

Connection Leak in MSSQL

Sukhpinder Singh
I'm Sukhpinder Singh, a passionate self-taught .Net developer from India. Skills C# | Microsoft Certified | Medium Blogger | .Net Core | Xamarin | ASP.Net | Angular
Originally published at singhsukhpinder.Medium ・1 min read

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

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.
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.

Follow me on

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

Discussion (0)