CODEWITHSUNDEEP
.netsqltimeout
Micke
Micke

Reputation: 49

SQL timeout in a .NET service

I have a very simple service that has always worked earlier but now I upgraded it to newer .NET version and it has stopped working with error message like this:

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.

I have tried to execute the stored procedure that the service tries to run and running it direct in ms sql server management studio takes less than 1 second so I don't understand what can be the problem? Clients using the same database (not the same stored procedure though) don't have any timeout problems while using them at the same time.

EDIT so you can see the code:

This is called when the service starts:

Private Sub SetupGetBatch1Command()
    GetBatch1Command = New System.Data.SqlClient.SqlCommand("spgetTheData1")
    GetBatch1Command.CommandType = Data.CommandType.StoredProcedure
End Sub

And this with timed intervals:

Private Function GetBatch1() As DataSet
    Dim dataset As New DataSet
    Dim adapter As Data.SqlClient.SqlDataAdapter
    Dim cn As New System.Data.SqlClient.SqlConnection(connectionstring())
    GetBatch1Command.Connection = cn
    adapter = New Data.SqlClient.SqlDataAdapter(GetBatch1Command)
    adapter.Fill(dataset)
    Return dataset
End Function

I haven't done this and don't know so much about it but I guess I should at least follow the advice below and add cn.dispose() somewhere?

There are also other similar database connections, all done in the exact same way as the example above.

Upvotes: 0

Views: 313

Answers (1)

to StackOverflow
to StackOverflow

Reputation: 124794

This is almost always a result of failing to dispose/close a connection object. Go through your code and make sure all your SqlConnection objects are disposed, preferably with a using statement:

using (SqlConnection c = new SqlConnection(...))
{
    // use the connection...
    ...
} // c.Dispose() will be called automatically here, even if an exception is thrown.

I suspect the upgrade to a new .NET version is either a red herring, or is only indirectly causing the problem: for example, you might be getting exceptions that you weren't getting before, and your code might be failing to dispose/close connections properly when an exception is thrown.

Upvotes: 1

Related Questions