Puneet Ghanshani

Chief Architect at Microsoft

3 minute read

There are several reasons to have this error pop up on your website.  The fix requires thorough analysis of the behavior.

First, check for DB code

Database access code must be within the 'using' block.  In case this is not done, it may keep your database connections live even when your code is not performing any database transaction.

Check out for following in your data access layer

  • If you have your own wrapper over any database library, the wrapper must inherit IDisposable.  In the dispose method, it should close the database connections and explicitly assign 'null' to the database and related objects.
  • Use the 'using' block at all places. If you are not using the 'using' block, use the try..catch..finally block

    [sourcecode language='csharp']
    using (SqlConnection conn = new SqlConnection)
    {
    conn.Open();doSomething();
    } // Connection is disposed and closed here, even if an exception is thrown[/sourcecode]or

    [sourcecode language='csharp']

    SqlConnection conn = new SqlConnection(myConnectionString);
    try
    {
    conn.Open();
    doSomething(conn);
    }
    finally
    {
    conn.Close();    // This line will get called in any case — success/failure
    }

    [/sourcecode]

  • If you are using DataReader, make sure you are creating the object of data reader from the database connection class, something likereader = databaseConnection.CreateReader();

[ad#between post content]

Second, check the Database Connection Timeouts

Check the Remote Query Timeout at the database end.  Generally, the default value would be 600 seconds.  You can try increasing it as shown below
[sourcecode language='sql']
EXEC SP_CONFIGURE 'remote query timeout', 1800
reconfigure
EXEC sp_configureEXEC SP_CONFIGURE 'show advanced options', 1
reconfigure
EXEC sp_configureEXEC SP_CONFIGURE 'remote query timeout', 1800
reconfigure
EXEC sp_configure[/sourcecode]

More debugging?

Modify your connection string to add an application name to connect to database.  You can even use a module identifier within your application to detect which module of your application is leaving the connections open

[sourcecode language='csharp']

Data Source=myServer; Initial Catalog=myDB; User Id=myUsername; Password=myPassword; Application Name=myApp;

[/sourcecode]

Then with a SQL query, you can find out the program name and the open connections

[sourcecode language='sql']

SELECT Program_name, count(*)
FROM master..sysprocesses
GROUP BY Program_name
ORDER BY count(*) desc

[/sourcecode]

Final try!

Though not really recommended, you can increase the pool size to 100 in the connection string and try it.

You May Also Enjoy