"Timeout expired" for simple ADO.NET SQL Server query

[Nils Magnus Englund]

Hi,

I've made a HttpModule which deals with user authentication. On the first
request in a users session, it fetches data from a SQL Server using the
following code:


using (SqlConnection connection = new
SqlConnection(ConfigurationSettings.AppSettings["Database.Connection"]))
{
connection.Open();
SqlCommand sqlCommand = new SqlCommand("GetAdvisorEnterpriseLogin",
connection);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add("@InputUsername",
HttpContext.Current.User.Identity.Name);
SqlDataReader reader = sqlCommand.ExecuteReader();
reader.Read();
username = (string) reader["Username"];
password = (string) reader["Password"];
reader.Close();
}

The "GetAdvisorEnterpriseLogin" procedure is a really simple one, and hardly
takes 0.1 second to run.
However, the first 1-3 times I access the page after a recompile (and once
in a while otherwise), I get the following exception:

Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding. 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.Data.SqlClient.SqlException: Timeout expired. The
timeout period elapsed prior to completion of the operation or the server is
not responding.

I also get the exception after not using the system for a while, but I
haven't experienced when the system is "in use", i.e. when the previous
request was no more than a couple of minutes ago.

Does anyone know what could cause this error, and how I can prevent it from
happening?



Regards,
Nils Magnus Englund

 

[Kevin Spencer]

This can be alleviated by increasing the CommandTimeout. Of course, this
isn't necessarily the best thing to do, but it can be done piecemeal (on a
per-Command basis in your code), in the web.config file, or at a higher
level, although I wouldn't recommend that.

One way to diagnose the issue is to log the time that the Command takes to
execute. This can be done by getting DateTime.Now prior to opening the
Connection, and using DateTime.Subtract() after executing the Command, to
get the TimeSpan elapsed during execution. Example (Hope you don't mind if I
didn't cut out all the stuff you're not interested in):

Overloads Shared Function ExecuteSP(ByVal strProcedureName As String, _
ByVal UseTransaction As Boolean, ByVal CommandTimeout As Integer, _
ByRef SecondsToRun As Integer, Optional ByVal aryParameters As sParam()
= Nothing, _
Optional ByVal p_strConnectionString As String = "") As Boolean

Dim objConn As SqlConnection
Dim objCommand As SqlCommand
Dim objTrans As SqlTransaction = Nothing
Dim strCString As String = p_strConnectionString
Dim intCt As Integer
Dim d As System.DateTime = System.DateTime.Now()
Dim ts As TimeSpan

Try
If strCString = "" Then strCString = ConnectionString
objConn = New SqlConnection(strCString)
objConn.Open()
objCommand = objConn.CreateCommand()
If UseTransaction Then
objTrans = objConn.BeginTransaction()
End If
objCommand.Connection = objConn
If UseTransaction Then
objCommand.Transaction = objTrans
End If
If CommandTimeout > 0 Then objCommand.CommandTimeout =
CommandTimeout

Try
objCommand.CommandText = strProcedureName
objCommand.CommandType = CommandType.StoredProcedure
If Not IsNothing(aryParameters) Then
For intCt = 0 To aryParameters.Length - 1
objCommand.Parameters.Add(aryParameters(intCt).Name,
aryParameters(intCt).Type).Value = aryParameters(intCt).Value
objCommand.Parameters(objCommand.Parameters.Count -
1).Direction = aryParameters(intCt).Direction
objCommand.Parameters(objCommand.Parameters.Count -
1).Size = aryParameters(intCt).Size
Next
End If
objCommand.ExecuteNonQuery()
If UseTransaction Then
objTrans.Commit()
End If
Catch ex0 As SqlException
Utilities.LogError(GetSqlException(ex0))
Utilities.HandleError(ex0, False, "Exception " & _
" occurred Rolling Back Stored Procedure " & _
strProcedureName)
If UseTransaction AndAlso ex0.Message.IndexOf("Timeout
expired") < 0 Then
Try
objTrans.Rollback()
Catch ex1 As SqlException
Utilities.LogError(GetSqlException(ex0))
Utilities.HandleError(ex0, False, "Exception " & _
" occurred Rolling Back Stored Procedure " & _
strProcedureName)
Return False
Catch invex As InvalidOperationException
Utilities.HandleError(invex, False, _
"InvalidOperation Exception occurred during
rollback of transaction associated with Stored Procedure '" & _
strProcedureName & "'")
Return False
Catch ex As Exception
Utilities.HandleError(ex, True, "Exception of Type "
& _
ex.GetType().ToString() & " occurred Executing
Stored Procedure " & _
strProcedureName)
Return False
End Try
End If
Return False
End Try
Return True
Catch ex2 As Exception
Utilities.HandleError(ex2, True, "Exception of Type " & _
ex2.GetType().ToString() & " occurred Executing Stored
Procedure " & _
strProcedureName)
Return False
Finally
CloseConn(objConn, objCommand)
If Not IsNothing(objTrans) Then objTrans.Dispose()
ts = System.DateTime.Now.Subtract(d)
SecondsToRun = CInt(ts.TotalSeconds)
End Try
End Function

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

Hi,

I've made a HttpModule which deals with user authentication. On the first
request in a users session, it fetches data from a SQL Server using the
following code:


using (SqlConnection connection = new
SqlConnection(ConfigurationSettings.AppSettings["Database.Connection"]))
{
connection.Open();
SqlCommand sqlCommand = new SqlCommand("GetAdvisorEnterpriseLogin",
connection);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add("@InputUsername",
HttpContext.Current.User.Identity.Name);
SqlDataReader reader = sqlCommand.ExecuteReader();
reader.Read();
username = (string) reader["Username"];
password = (string) reader["Password"];
reader.Close();
}

The "GetAdvisorEnterpriseLogin" procedure is a really simple one, and
hardly
takes 0.1 second to run.
However, the first 1-3 times I access the page after a recompile (and once
in a while otherwise), I get the following exception:

Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding. 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.Data.SqlClient.SqlException: Timeout expired.
The
timeout period elapsed prior to completion of the operation or the server
is
not responding.

I also get the exception after not using the system for a while, but I
haven't experienced when the system is "in use", i.e. when the previous
request was no more than a couple of minutes ago.

Does anyone know what could cause this error, and how I can prevent it
from
happening?



Regards,
Nils Magnus Englund

 

[Nils Magnus Englund]

This can be alleviated by increasing the CommandTimeout. Of course, this
isn't necessarily the best thing to do, but it can be done piecemeal (on a
per-Command basis in your code), in the web.config file, or at a higher
level, although I wouldn't recommend that.

Hi Kevin,

Thanks for your reply, but unfortunately, increasing CommandTimeout or
setting it to 0 didn't help at all. When the code doesn't throw a Timeout
exception, the procedure takes under 0.1 seconds to run - which should be
well within the default CommandTimeout (which I believe is 30 seconds)?


Regards,
Nils Magnus Englund

 

[Kevin Spencer]

Hi Nils,

It sounds like the problem may be on your SQL Server. You'll have to do some
diagnosis there. Sometimes, for example, a SQL Server can get very busy
doing database backups, Transaction Log backups, or even handling other jobs
or client tasks. Another possible issue might be network latency. If the
network connection goes down, or gets bogged down for some reason, it can
cause the Command to time out eventually. The CommandTimeout is a property
of the SqlCommand class, and indicates how long it will wait for a response.
It does nothing on the SQL Server itself.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.