Currently all connections getting "Timeout expired"
4
votes
2
answers
40017
views
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 ExecuteAsync(ExecutionType executionType, CommandType commandType, string commandText, IsolationLevel isolationLevel, SqlParameter[] parameters, Func 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();
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;
}
}
}
}
Asked by Imran Qadir Baksh - Baloch
(1319 rep)
Oct 29, 2014, 10:47 AM
Last activity: Jan 27, 2023, 01:05 PM
Last activity: Jan 27, 2023, 01:05 PM