What is it?
Recently we had came across an issue with a WCF application. After a period of normal operation, any SQL operations through SQLConnection objects returned weird errors in production environment.The error text varies and below are the typical ones that get displayed.
· NB: Format of the initialization string does not confirm to specification starting at index 0
· A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.
· Procedure or function has too many arguments specified
· Input string was not in correct format
· Internal connection fatal error.
· Connection property has not been initialized
· A transport-level error has occurred when sending the request to the server.
· The connection is closed
This is a typical scenario of a Zombie (Broken) SQL connection get reused over and over again from the connection pool. Broken connections can happen in certain scenarios. Say we’ve a valid SQLConnection in ‘Open’ state to the database server. Suppose the ‘Database’ server got restarted or went down, then the ‘SQLConnection’ at our hand becomes a broken connection. Now any ‘SQL Operations’ requested through that connection will end with errors similar to the above listed ones. Even if the database server comes online, we cannot re-use this ‘SQLConnection’ as it is in broken state. It should be discarded forever. Luckily Zombie connections are automatically removed from connection pools by the connection pooling framework.
So how does the above issue happens in the first place? This will happen, due to the below ways.
Why it is happening?
1. Programmer forgot to release (dispose) any SQL related objected after use.
This may usually happen during an exception, where programmer forgot to release the object inside the ‘catch’ section of the ‘Try’ block.
If we forgot to release an SQL connection, it is said to be a leaky connection, which will not be properly tracked by the connection pooling mechanism. These connections are get reused over the course of time, without any recycling and if in-turn become a broken connection caused the above failures.
2. App-Pool corresponding to your application in IIS, not get recycled properly due to some configuration settings
This wont typically happen for web applications as App-Pool get recycled automatically.
But for certain applications like WCF, and under certain configurations (eg. Instance Context Mode configured as ‘Singleton’) , Automatic recycling will not happen and over a period of time your resources get leaked until you manually recycle the pool.
So what could be the solution? The full proof solution consists of two parts
What is the resolution?
a. Clear and recreate the connection pool, once a broken connection has encountered.
While creating a connection, always validate it’s state after the ‘Open’ call. If the state is ‘Broken’ or ‘Not-Open’, Clear the ‘Connection Pools’ and re-create the connection in the new pool.
b. Properly release all SQL related objects after use, in every scenarios.
We can use ‘Using’ blocks to properly release the objects, no matter whether an exception has occurred or not.
Sample Code
The sample code is given below.
Note: This example is related SQLConnection object. If you’re using other frameworks like LINQToSQL, EntityFramework e.t.c, Check the state of the ‘Connection’ property of the ‘DataContext’ or somewhat similar object.
a. Sample, recreating the connection pool, once a broken connection has encountered.
//This is the helper function to get the connection properly from the connection pool.
//It clears and recreates the pool, if necessory (eg. Broken connection)
public SqlConnection GetPooledConnection()
{
SqlConnection con = new SqlConnection(connectionString);
if (con.State == ConnectionState.Broken)
{
SqlConnection.ClearAllPools();
con = new SqlConnection(connectionString);
}
con.Open();
if (con.State != ConnectionState.Open)
{
SqlConnection.ClearAllPools();
con = new SqlConnection(connectionString);
con.Open();
}
return con;
}
b. Sample. Properly release all SQL related objects after use, in every scenarios.
//This is a sample function that uses, the connection and SQL objects.
//This also demonstrates, the proper disposal of SQL objects
public void Usage()
{
try
{
using (SqlConnection con = GetPooledConnection())
{
using (SqlTransaction tran = con.BeginTransaction())
{
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.Transaction = tran;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
//Read your data here
}
//Do your data operations using cmd object
}
//Commit updates on graceful exit
tran.Commit();
}
catch
{
//Rollback on errors
tran.Rollback();
//Log your errors
throw;
}
}
}
}
catch
{
//Log your errors
throw;
}
}
