4

I have an application which connects to SQL Server. Currently I am getting,

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.

When I ran,

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
     DB_NAME(dbid) ='MyDb'
GROUP BY 
    dbid, loginame 

DBName  NumberOfConnections LoginName
MyDb    10                     sa                                                                                                                              
MyDb    109                   MyUser

The status of all process is sleeping and cms is AWAITING COMMAND

Here is my code,

private async Task<object> ExecuteAsync<T>(ExecutionType executionType, CommandType commandType, string commandText, IsolationLevel isolationLevel, SqlParameter[] parameters, Func<IDataReader, T> callback = null)
{
    var stopwatch = new Stopwatch();
    stopwatch.Start();
    using (var connection = new SqlConnection(_settings.DatabaseConnectionString))
    {
        using (var command = new SqlCommand(commandText, connection) {CommandType = commandType})
        {
            command.Parameters.AddRange(parameters);
            await connection.OpenAsync().ConfigureAwait(false);
            command.CommandTimeout = _settings.CommandTimeout;
            var transaction = connection.BeginTransaction(isolationLevel);
            command.Transaction = transaction;
            try
            {
                object result;
                switch (executionType)
                {
                    case ExecutionType.Reader:
                        var reader = await command.ExecuteReaderAsync().ConfigureAwait(false);
                        using (reader)
                        {
                            var list = new List<T>();
                            while (reader.Read())
                            {
                                if (callback != null)
                                {
                                    var item = callback(reader);
                                    if (item != null)
                                    {
                                        list.Add(item);
                                    }
                                }
                            }
                            result = list;
                        }
                        break;
                    case ExecutionType.NonQuery:
                        result = await command.ExecuteNonQueryAsync().ConfigureAwait(false);
                        break;
                    default:
                        result = await command.ExecuteScalarAsync().ConfigureAwait(false);
                        break;
                }
                transaction.Commit();
                stopwatch.Stop();
                var elapsed = stopwatch.Elapsed;
                if (elapsed.Seconds > 2)
                {
                    _logger.Log(string.Format("{0} took {1} time", command.CommandText, elapsed));// only log if it tooks more than 2 seconds
                }
                return result;
            }
            catch (Exception exception)
            {
                _logger.Log(exception);
                transaction.Rollback();
                throw;
            }
        }
    }
}
8

It looks like you have an application that isn't properly closing or disposing of the SqlConnection objects. By default, SqlConnection has a max pool size of 100.

The fix here would be to work with the application to find out why connections aren't being cleaned up, as they are still "active" in the particular connection pool, which is why you are unable to grab another one, because there are no inactive connections in the pool to use.

The application should be making a call to SqlConnection.Close() or SqlConnection.Dispose() in order to release the connection and mark it as "inactive".

I wrote an extensive blog post (Connection Pooling for the SQL Server DBA) about connection pooling, and it should shed some light on why you are seeing what you are seeing, and also the programmatic fix to the issue.

5

An alternative, is if you are processing a higher volume of records, you may actually be getting a misleading error. It could be that you have used up all of your ports on your PC, you can check this by running Sysinternals TCPView.

If you see thousands of TIME_WAIT, this is a possible cause.

In essence, after your application closes its connection to the database (at end of using (var connection = new SqlConnection(_settings.DatabaseConnectionString))), TCP will wait for nearly 4mins before the port that was in use is free to be used again.

From memory you would need to process around 16,000 executions in under 4 mins for this to occur (it will depend on your OS version and what else you have running on that PC).

  • Thanks. I am not processing huge amount of data. My app-domain is restarted. So, everything is started to work again. – user960567 Oct 29 '14 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.