SQL 2005 random connection timeouts / best practice regarding db timeouts

Question

I am using the ADO.Net SqlCommand type and I set CommandTimeout to 30 seconds.

My problem is that the connection / command does not allow time synchronization, causing unhandled exceptions that crash my system!

The data I'm trying to retrieve is critical to the system - so I want to fix timeouts, rather than adding logic to reprocess exceptions.

So my question is; How to avoid / fix database timeout problems?

I do not want to set the timeout to a value greater than 30 seconds, since I have a critical time code.

thanks

Answer 1

• Exclude processing so that they do not crash your system.
• Correct your database calls so that they do not log out

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

If you call within 30 seconds, this means that you are either performing LOT processing or blocking all the time. Most likely, you are blocked all the time. To reduce the lock, decrease the volume and duration of the locks. A more detailed answer to such a general question would mean basically repeating all the principles of transaction processing theory ...

Answer 2

, ... , ( SQL Server 2005).

, ( , ), Commit Transaction, ( , ... / ):

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

, ...

Answer 3

Remus, , .

. , ,

Use sql profile to optimize queries Use stored procedures, not inline code Optimize the database schema, include an index where a column can help. Try swapping if you are returning a lot of rows. Return only what you need. If the timeout isn’t due to blocking here is a great article -

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

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