Stored Proc and SqlCommand Timeout

Question

If I run a stored proc using the SqlCommand and the SqlCommand times out does the StoredProc continue to execute or does it get force to quit when the SqlCommand disconnects?

Answer 1

My instinct said that the procedure would still execute so I threw together a simple test.

SQL:

Create Procedure TestDelay
AS

waitfor delay '00:00:40'

update table_1
set dt = getdate()

And in VB.Net (same as C# for this purpose):

    Dim con As New SqlConnection(myconnectionstring)
    Dim com As New SqlCommand("TestDelay", con)
    com.CommandType = CommandType.StoredProcedure
    con.Open()
    Try
        com.ExecuteNonQuery()
    Catch ex As Exception
        con.Close()
        Response.Write(ex.Message)
    End Try

The result? The procedure did not complete after the timeout. I checked what was happening during a trace in SQL profiler and sure enough SQL appears to wrap the call in a transaction and must roll that transaction back on the timeout.

Note: This test was run against SQL 2005 but I'd suspect the results to be similar in other versions.

Answer 2

As brendan stated, the client sends an "abort" and processing stops dead. As simple as that.

However, it's more complex than that...

Any transactions are not rolled back by default and locks are just left there until the connection is closed. If returned to the connection pool and reused, then this does not count as closing.

This is why SET XACT_ABORT ON (other questions SO1, SO2) is recommended