Checking the number of SQL Server connections for SQL connection "Timeout period elapsed" error

By Steve Endow

 I recently had to troubleshoot an issue with a Dynamics GP third party product that would hang with the error:

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 max pool size was reached.

Please note the phrase "prior to obtaining a connection from the pool".  This is not a command timeout, it is a connection timeout due to a lack of available connections in the application's connection pool.

To confirm this problem, I ran the following SQL query that I found on some helpful forum post (don't recall where):

SELECT

    DB_NAME(dbid) as DBName,

    COUNT(dbid) as NumberOfConnections,

    loginame as LoginName

FROM

    sys.sysprocesses

WHERE

    dbid > 0

GROUP BY

    dbid, loginame

This query produces a very nice result set that allows you to quickly and easily see active SQL connections.

In my case, the third party application was opening 100 connections to its database, so if you tried to process more than 100 records, it would hang and display the connection timeout error above.

This problem occurred because the application was not properly closing its connection as it looped through hundreds of records.

The query helped me quickly confirm the issue and point the developers to the source of the error.


UPDATE: Please check out David Musgrave's related post about how this problem can occur with VBA modifications in Dynamics GP:

http://blogs.msdn.com/b/developingfordynamicsgp/archive/2014/04/23/more-on-sql-server-connection-issues-with-microsoft-dynamics-gp.aspx

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com