SQL 2005 random connection timeouts / best practice regarding db timeouts

Question

I am using the ADO.Net SqlCommand type and I'm setting the CommandTimeout to 30 seconds.

My problem is that the connection/command keeps timing out causing unhandled exceptions that crash my system!

The data I am trying to retrieve is critical to the system – so I want to fix the timeouts rather than add exception handling retry logic.

So my question is; How do you avoid / fix database timeout issues?

I don’t want to set the timeout to a value greater than 30 seconds as I have time critical code.

Thanks

Answer 1

1. Handle exception so they don't crash your system
2. Fix your database calls so they don't time out

Both of the issues above must be implemented. A database call can always throw exceptions, no matter what precautions you take, so you must handle exception, period.

If you're calls take over 30 seconds it means you either do a LOT of processing, or you're blocked all the time. Most likely you're blocked all the time. To reduce blocking, reduce the scope and duration of your locks. Giving a more detailed response for such a generic question would mean basically reiterating through the all the principles of transaction processing theory...

Answer 2

My app was installed on a customer's network which was VERY unreliable... we had to retry a command's execution after a lost connection because the second time it usually passed through (we're talking SQL Server 2005 here).

Assumming you're using Transactions (if not then you should), here's my Commit Transaction wrapper which handles lost connections fairly well (could be optimized I guess... but its just a copy/paste from my code):

Public Shared Function SafeCommitRollback(ByVal Trans As SqlClient.SqlTransaction, Optional ByVal Action As TROperation = TROperation.Commit, Optional ByVal QuietMode As Boolean = False) As Boolean
    SafeCommitRollback = False
Dim TryRollback As Boolean = False
Dim ConnLost As Boolean = False
Dim msgErr As String = ""

If Action = TROperation.Commit Then
    Try
        Trans.Commit()
        SafeCommitRollback = True
    Catch ex As SqlClient.SqlException When ex.Class = 20 OrElse (ex.Class = 11 And ex.Number = -2)
        ConnLost = True
    Catch ex As System.InvalidOperationException When ex.Source = "System.Data" 'AndAlso ex.Message.StartsWith("Timeout expired.")
        ConnLost = True
    Catch ex As Exception
        TryRollback = True
        msgErr &= clsErrorHandling.ParseException(ex, True)
    End Try

    If ConnLost Then
        Try
            Trans.Commit()
            SafeCommitRollback = True
        Catch ex2 As Exception
            TryRollback = True
            msgErr &= clsErrorHandling.ParseException(ex2, True)
        End Try
    End If
Else
    TryRollback = True
End If


If TryRollback Then
    Try
        Trans.Rollback()
        If Action = TROperation.Rollback Then SafeCommitRollback = True
    Catch ex3 As Exception
        msgErr &= clsErrorHandling.ParseException(ex3)
    End Try
End If

    If Not QuietMode AndAlso msgErr.Trim <> "" Then clsMessageBox.ShowError(msgErr)
End Function

I hope this helps...

Answer 3

Agree with Remus, handle the timeout if it occurs.

There is a lot of scope for optimising the database query. It's not specific unless I have details, but you could try

Use sql profile to optimise queries Use Stored Procedures, not inline code Optimise database schema, include index on where column might help. Try paging if returning a lot of rows Only return what you need. If the timeout isn't due to blocking here's an excellent article -

http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1339694,00.html

http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm