Connection Pool limit exceeds Error

Sometimes below error occurs on our web applications hosted on IIS connected with SQL Server.

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.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: 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.

Basically, This error occurs whenever connection pool limit exceeds.This can be resolved by clearing connection pool.However this can be resolved using SQL Server by number of ways.

You can use

SqlConnection.ClearAllPools(); of .Net to clear pool.

SqlConnection.ClearAllPools() method empties the connection pool.If there are connections in use at the time of the call, they are marked appropriately and will be discarded (instead of being returned to the pool) when Close method  is called on them.

Sometimes you need to clear pool without modifying your code.So I create a utility for cleaning connection pool.A simple window form that make a connection to your database and empty database connection pool.

Use Code:

private void button1_Click(object sender, EventArgs e)
     {
         try
         {
             //Creating Connection
             SqlConnection con = new SqlConnection();
             //Connection Sring
con.ConnectionString = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}", txtData.Text, txtDatabaseName.Text, txtUserID.Text, txtpassword.Text);
             if (button1.Text == "Connect")
                {
                 //Open Connection
                 con.Open();
                 if (con.State == ConnectionState.Executing)
                 {
                     lblStatus.Text = "Connecting..........";
                 }
                 else if (con.State == ConnectionState.Open)
                 {
                     lblStatus.Text = "Connected";
                     button1.Text = "Disconnect";
                     btnClear.Enabled = true;
                 }
             }
             else
             {
                 con.Close();
                 lblStatus.Text = "Disconnected";
                 button1.Text = "Connect";
                 btnClear.Enabled = false;
             }
         }
         catch (Exception ex)
         {
             lblStatus.Text = ex.ToString();
         }
     }
 
     private void btnClear_Click(object sender, EventArgs e)
     {
         //Clear all Pools.
         SqlConnection.ClearAllPools();
         lblstatuspool.Text = "Pool Claered";
     }
 

 

Download Utility

Sourceforge:

https://sourceforge.net/projects/connectionpoolcleaner/files/latest/download?source=files

Direct Link:

https://downloads.sourceforge.net/project/connectionpoolcleaner/ClearConnectioPool.rar?r=&ts=1379070899&use_mirror=master