If my C# times out with a stored procedure call, does the procedure continue running?

Question

I have just a general type of question. If I have a C# application that calls a SQL Server stored procedure, and the C# application times out, does the procedure call on the server continue running to it's completion?

Cia

there are two different time outs ... there is a connection timeout and a command timeout – Colatitude

Really unclear what you mean by C# app "timing out". The stored procedure isn't going to be waiting on the C# app for anything. Once the SP starts it will run to completion. If however some C# code after executing the SP causes the app to freeze up and eventually terminate, and this occurs within an explicit transaction scope, then yes it would rollback. – Jobless

It is a CommandTimeout that is occurring. The procedure in question does not return any data, and in theory doesn;t need the C# app for anything other than starting the procedure call. There is no transaction, just calls the procedure. So you're saying if the CommandTimeout "times out", the procedure will continue to run? – Cia

Answer 1

No. Below is a reproduction. When the timeout occurs the running process will be killed, halting it immediately. If you do not have a transaction specified, work that has been done in the stored procedure prior to the timeout will be persisted. Similarly, if the connection to the server is severed by some outside force, SQL Server will kill the running process.

using (var conn = new SqlConnection(@"Data Source=.;Initial Catalog=Test;Integrated Security=True"))
{
    conn.Open();

    using (var setupTable = new SqlCommand(@"
        IF NOT EXISTS (
            SELECT *
            FROM
                sys.schemas s
                    INNER JOIN sys.tables t ON
                        t.[schema_id] = s.[schema_id]
            WHERE
                s.name = 'dbo' AND
                T.name = 'TimeoutTest')
        BEGIN
            CREATE TABLE dbo.TimeoutTest
            (
                ID int IDENTITY(1,1) PRIMARY KEY,
                CreateDate datetime DEFAULT(getdate())
            );
        END

        -- remove any rows from previous runs
        TRUNCATE TABLE dbo.TimeoutTest;", conn))
    {
        setupTable.ExecuteNonQuery();
    }

    using (var checkProcExists = new SqlCommand(@"
        SELECT COUNT(*)
        FROM
            sys.schemas s
                INNER JOIN sys.procedures p ON
                    p.[schema_id] = s.[schema_id]
        WHERE
            s.name = 'dbo' AND
            p.name = 'AddTimeoutTestRows';", conn))
    {
        bool procExists = ((int)checkProcExists.ExecuteScalar()) == 1;

        if (!procExists)
        {
            using (var setupProc = new SqlCommand(@"
                CREATE PROC dbo.AddTimeoutTestRows
                AS
                BEGIN

                    DECLARE @stop_time datetime;

                    SET @stop_time = DATEADD(minute, 1, getdate());

                    WHILE getdate() < @stop_time
                    BEGIN
                        INSERT INTO dbo.TimeoutTest DEFAULT VALUES;

                        -- wait 10 seconds between inserts
                        WAITFOR DELAY '0:00:10';
                    END

                END", conn))
            {
                setupProc.ExecuteNonQuery();
            }
        }
    }

    bool commandTimedOut = false;

    try
    {
        using (var longExecution = new SqlCommand("EXEC dbo.AddTimeoutTestRows;", conn))
        {
            // The time in seconds to wait for the command to execute.
            // Explicitly setting the timeout to 30 seconds for clarity.
            longExecution.CommandTimeout = 30;

            longExecution.ExecuteNonQuery();
        }
    }
    catch (SqlException ex)
    {
        if (ex.Message.Contains("Timeout"))
        {
            commandTimedOut = true;
        }
        else
        {
            throw;
        }
    }

    Console.WriteLine(commandTimedOut.ToString());

    // Wait for an extra 30 seconds to let any execution on the server add more rows.
    Thread.Sleep(30000);

    using (var checkTableCount = new SqlCommand(@"
        SELECT COUNT(*)
        FROM
            dbo.TimeoutTest t;", conn))
    {
        // Only expecting 3, but should be 6 if server continued on without us.
        int rowCount = (int)checkTableCount.ExecuteScalar();

        Console.WriteLine(rowCount.ToString("#,##0"));
    }
}

Console.ReadLine();

produces the following output

True
3

even though running the stored procedure from Management Studio will add 6 rows in the one minute time frame.

Macrobiotics

Answer 2

Short answer is Yes .... here is some info to back up my claim

There are actually several places where a application can 'time out' but one is a Command Execution time out ...

command execution time out - This property is the cumulative time-out for all network reads during command execution or processing of the results. A time-out can still occur after the first row is returned, and does not include user processing time, only network read time.

A Command is not going to rollback on its own if it times out. This will need a transaction around the code if it times out.

if a timeout can occur when rows are being returned than means a timeout can occur anytime C# is not going to tell SQL Server to stop running the command. Things can be done about that such as wrapping the command in a transaction

Source: https://blogs.msdn.microsoft.com/mattn/2008/08/29/sqlclient-timeouts-revealed/ ... and experience

Colatitude

Most of this description you linked to is if retrieving rows. In my case the stored procedure doesn't return any results. It kicks off a nightly import job. So if the C# CommandTimeout times out while that procedure is running on the server, will it continue to run on the server even though the C# connection to it timed out? – Cia

I think the description ... can still occur after the first row means it can occur anytime before the first row is returned as well. All a timeout means is that this is taking along time more than it should – Colatitude

Answer 3

If you are using the SQlCommand Class, once the app times out , the query execution will be rolled back.

Sloe

not sure i agree with this it depends if the query is wrapped in a transaction – Colatitude

Answer 4

My thoughts about that are that it's all about the connection opened by the call to the procedure. If your code is executed within a using block or if it is garbage collected then i think that the execution of the SP will be rolled back.

Cementation

Answer 5

            Conn = new SqlConnection(ConnStr);
            Conn.Open();
            myCommand = new SqlCommand();
            myCommand.CommandTimeout = 180000;
            myCommand.Connection = Conn;
            myCommand.CommandType = System.Data.CommandType.StoredProcedure;

Individuate