SQL 2005 random connection timeouts / best practice regarding db timeouts

Go To StackoverFlow.com


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.


2009-06-16 10:02
by SuperSuperDev1234


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

2009-06-16 10:18
by Remus Rusanu
Good answer, better queries and indexes on tables might help you - Fredrik Leijon 2009-06-16 10:44
You can never be sure that timeouts will not occur, sometimes these things happen even with the best code. Always Handle Exceptions - AllenG 2009-06-16 20:47


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
        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
            SafeCommitRollback = True
        Catch ex2 As Exception
            TryRollback = True
            msgErr &= clsErrorHandling.ParseException(ex2, True)
        End Try
    End If
    TryRollback = True
End If

If TryRollback Then
        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...

2009-06-16 10:23
by Rodolfo G.
You can convert it to C# using this tool: http://www.developerfusion.com/tools/convert/vb-to-csharp - Rodolfo G. 2009-06-16 10:39


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 -



2009-06-16 10:24
by Stuart