SqlConnection error -- Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool

Question

I have a C# Windows service that repeatedly calls a SQL Server stored procedure through the code below. When running the stored procedure manually, it returns the appropriate data set in under one second.

When the service runs, I can see it executing the stored procedure multiple times in sys.dm_exec_requests and sys.sysprocess, and then finishing properly.

However, viewing it in sys.dm_exec_sessions, the connections are still staying open and seem to max out at 50 sessions, even though the Max Pool Size set in the connection string is set at 250.

In researching this, it seems that I shouldn't need to close or dispose of the connection when using a USING statement (there's a good explanation here). But this doesn't seem to be working for me. I've also tried doing it without the USING but with a TRY...FINALLY including the CLOSE(). However this didn't work either.

In all cases, I can see the stored procedures execute and finish but the connections seem to stay open. Am I missing something? Is there any way I can make sure to close the connections? I understand what the error in the subject is telling me, but my attempts to resolve it aren't working.

DataTable dt = new DataTable();

try
{
    string connStr = ConfigurationManager.AppSettings["ConnectionString"].ToString();

    using (SqlConnection conn = new SqlConnection(connStr))
    {
        SqlCommand sqlComm = new SqlCommand(SQL, conn);

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = sqlComm;
        da.SelectCommand.CommandTimeout = 720;

        da.Fill(dt);
    }
}
catch (Exception ex)
{
    SendErrorEmail("GetData function", ex.ToString());
}

return dt;

Here's the connection string I'm using...

<add key="ConnectionString" value="Data Source=*.*.*.*;Database=*****;Persist Security Info=True;User ID=*****;Password=*****;Max Pool Size=250;Application Name=SQLService;" />

Try specifically opening the connection with conn.Open() before calling da.Fill(dt). DbDataAdapter does some interesting things based on the original connection state. Look at QuietOpen and QuietClose methods https://referencesource.microsoft.com/#System.Data/fx/src/data/System/Data/Common/DbDataAdapter.cs,555a8f558f3363ed — Mufaka, Jun 07 '21 at 18:40
What connection string are you using? Please provide it, but omit any sensitive information with asterisks. — mason, Jun 07 '21 at 18:46
@Mufaka I tried added the `conn.Open()` but unfortunately I had the same results. — WAMLeslie, Jun 07 '21 at 18:50
`but the connections seem to stay open` ← The managed connection will be closed but the unmanaged connection can exist as long as the application is loaded in memory. If you are looking on Sql Server then you could see an open connection which would lead you down the wrong path. Unmanaged connections are managed by the CLR, your code manages the Managed connection instances. — Igor, Jun 07 '21 at 18:55
@Igor Thanks for the info. That makes sense to me. However, why would I still be getting the error in the subject then if what I'm seeing is a non-issue? Here's the error I get repeatedly `System.InvalidOperationException: 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.` — WAMLeslie, Jun 07 '21 at 19:05
It could be that there is more than just this piece of code that is executing a statement against that database. You can sql profiler to see what else is coming in after you reset your application(s) to clear the connections (or kill them from SSMS). Based on the application or user or server or sql text you will probably discover some other piece of code that is not closing connections after use. Or perhaps an outdated version of the same app if there was ever a version where the using block was not implemented around the SqlConnection instance. — Igor, Jun 07 '21 at 19:07
@Igor There are other applications hitting the same database, but I do monitor the activity and there are no error/blocks/etc on a normal basis. The only time these errors occur is when running this particular service and it's a brand new one in development, so there are no other versions of it. — WAMLeslie, Jun 07 '21 at 19:11
Are there any other pieces of code in the same app that touch this database? Do they have the proper using statements, or dispose in a finally block? — mason, Jun 07 '21 at 19:14
I would structure your code as follows seeing as all these types implement `IDisposable`. `using (SqlConnection conn = new SqlConnection(connStr)) using (SqlCommand sqlComm = new SqlCommand(SQL, conn)) using (SqlDataAdapter da = new SqlDataAdapter())`. You can stack them and then use an open `{` after the last one, that will line them up vertically in the IDE instead of the nested view. — Igor, Jun 07 '21 at 19:15
@Igor Thanks again for the suggestion. I just tried it but unfortunately got the same result. I'm continuing to get that same error message. — WAMLeslie, Jun 07 '21 at 19:26
How is this code being invoked? Do you have this code being invoked from multiple threads at the same time? — mason, Jun 07 '21 at 19:28
Hmm, then I would start looking into my threading approach and see if the threading logic is calling this database logic multiple times concurrently. Might help if you break this down into a [mcve] that one can drop into a console app and reproduce the issue. — mason, Jun 07 '21 at 19:34

SqlConnection error -- Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool

0 Answers0