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

Discussion in 'ASP .Net' started by Nils Magnus Englund, Sep 12, 2005.

  1. 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, Sep 12, 2005
    #1
    1. Advertising

  2. 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.

    "Nils Magnus Englund" <> wrote in message
    news:%23Dh$...
    > 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, Sep 12, 2005
    #2
    1. Advertising

  3. "Kevin Spencer" <> wrote:
    > 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
     
    Nils Magnus Englund, Sep 12, 2005
    #3
  4. 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.

    "Nils Magnus Englund" <> wrote in message
    news:%...
    > "Kevin Spencer" <> wrote:
    >> 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, Sep 12, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Shahab Gerami

    ado.net Timeout expired

    Shahab Gerami, Jul 3, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    447
    Shahab Gerami
    Jul 3, 2003
  2. Bob Johnson
    Replies:
    0
    Views:
    3,797
    Bob Johnson
    Aug 7, 2003
  3. Nils Magnus Englund
    Replies:
    0
    Views:
    785
    Nils Magnus Englund
    Sep 15, 2005
  4. David Morgan

    ASP + SQL Server - Timeout Expired

    David Morgan, Oct 1, 2003, in forum: ASP General
    Replies:
    1
    Views:
    169
    Bob Barrows
    Oct 1, 2003
  5. Pablo Q.
    Replies:
    0
    Views:
    155
    Pablo Q.
    Sep 18, 2008
Loading...

Share This Page