How to catch SQLServer timeout exceptions

Question

I need to specifically catch SQL server timeout exceptions so that they can be handled differently. I know I could catch the SqlException and then check if the message string Contains "Timeout" but was wondering if there is a better way to do it?

try
{
    //some code
}
catch (SqlException ex)
{

    if (ex.Message.Contains("Timeout"))
    {
         //handle timeout
    }
    else
    {
         throw;
    }
}

Are you looking for a ConnectionTimeout or a CommandTimeout, ie are you expecting the connection to fail or the executed command to fail? — edosoft, Sep 15 '08 at 12:49
I'm looking for a CommandTimeout, which is set to a default of 30 secs i think — brodie, Sep 15 '08 at 23:14

score 181 · Accepted Answer · edited Sep 26 '16 at 16:37

181

To check for a timeout, I believe you check the value of ex.Number. If it is -2, then you have a timeout situation.

-2 is the error code for timeout, returned from DBNETLIB, the MDAC driver for SQL Server. This can be seen by downloading Reflector, and looking under System.Data.SqlClient.TdsEnums for TIMEOUT_EXPIRED.

Your code would read:

if (ex.Number == -2)
{
     //handle timeout
}

Code to demonstrate failure:

try
{
    SqlConnection sql = new SqlConnection(@"Network Library=DBMSSOCN;Data Source=YourServer,1433;Initial Catalog=YourDB;Integrated Security=SSPI;");
    sql.Open();

    SqlCommand cmd = sql.CreateCommand();
    cmd.CommandText = "DECLARE @i int WHILE EXISTS (SELECT 1 from sysobjects) BEGIN SELECT @i = 1 END";
    cmd.ExecuteNonQuery(); // This line will timeout.

    cmd.Dispose();
    sql.Close();
}
catch (SqlException ex)
{
    if (ex.Number == -2) {
        Console.WriteLine ("Timeout occurred");
    }
}

edited Sep 26 '16 at 16:37

James Wierzba

16,176
14
79
120

answered Sep 15 '08 at 13:09

Jonathan

25,873
13
66
85

Yes, that's pretty much what I'm doing at the moment, but it's not very elegant checking for -2 – brodie Sep 15 '08 at 23:20
12

Download Red Gate's Reflector, and search for TIMEOUT_EXPIRED. It lives in System.Data.SqlClient.TdsEnums, and its value is -2. :o) – Jonathan Sep 18 '08 at 08:14
2

For those who do not have access to Reflector: [link](http://www.dotnetframework.org/default.aspx/4@0/4@0/untmp/DEVDIV_TFS/Dev10/Releases/RTMRel/ndp/fx/src/Data/System/Data/SqlClient/TdsEnums@cs/1305376/TdsEnums@cs) – ankitk Nov 14 '13 at 18:48
6

@brodie That's why you should make a constant for it and you can explain where the "magic" value came from in a comment on the constant. – Jason L. Mar 27 '17 at 18:35
The error numbers are documented here: https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors?view=sql-server-ver16 (it is -2 for execution timeout) – Rosdi Kasim Sep 22 '22 at 03:54

score 25 · Answer 2 · answered Feb 11 '19 at 16:18

25

Updated for c# 6:

    try
    {
        // some code
    }
    catch (SqlException ex) when (ex.Number == -2)  // -2 is a sql timeout
    {
        // handle timeout
    }

Very simple and nice to look at!!

answered Feb 11 '19 at 16:18

John Evans

383
3
10

Thanks... I like your suggestion and use it. – Rubens Feb 19 '21 at 19:35

Roland Pihlakas · Answer 3 · 2016-08-04T23:35:10.797

here: http://www.tech-archive.net/Archive/DotNet/microsoft.public.dotnet.framework.adonet/2006-10/msg00064.html

You can read also that Thomas Weingartner wrote:

Timeout: SqlException.Number == -2 (This is an ADO.NET error code)
General Network Error: SqlException.Number == 11
Deadlock: SqlException.Number == 1205 (This is an SQL Server error code)

...

We handle the "General Network Error" as a timeout exception too. It only occurs under rare circumstances e.g. when your update/insert/delete query will raise a long running trigger.

score 0 · Answer 4 · edited Dec 23 '15 at 19:06

0

Whats the value for the SqlException.ErrorCode property? Can you work with that?

When having timeouts, it may be worth checking the code for -2146232060.

I would set this up as a static const in your data code.

edited Dec 23 '15 at 19:06

Just Do It

461
1
7
18

answered Aug 27 '08 at 07:20

Rob Cooper

28,567
26
103
142

2

Looking at the docs for ErrorCode, it seems to me that it's reporting Interop-Level errors. So it may be more on the level of COM errors or that a provider encountered an exception (generally) instead of a specific error relating to what you're doing. – Eric Tuttleman Jun 18 '10 at 18:23
@Eric is correct - that is an HRESULT code for the SqlException type, not for the source of the exception. – codekaizen Dec 16 '11 at 23:56

score 0 · Answer 5 · answered Oct 30 '19 at 08:36

I am not sure but when we have execute time out or command time out The client sends an "ABORT" to SQL Server then simply abandons the query processing. No transaction is rolled back, no locks are released. to solve this problem I Remove transaction in Stored-procedure and use SQL Transaction in my .Net Code To manage sqlException

score -2 · Answer 6 · answered Nov 13 '19 at 19:29

-2

When a client sends ABORT, no transactions are rolled back. To avoid this behavior we have to use SET_XACT_ABORT ON https://learn.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-ver15

answered Nov 13 '19 at 19:29

Srinivas Pakalapati

1

1

It does not answer the question which is how to catch a command timeout in the c# code – Jonathan Nov 13 '19 at 20:22

How to catch SQLServer timeout exceptions

6 Answers6

Linked

Related