Saved Proc and SqlCommand Timeout

Question

If I started the stored procedure using SqlCommand and SqlCommand time, does StoredProc continue to execute or force shutdown when SqlCommand shuts down?

Answer 1

My instinct said that the procedure would still be executed, so I put 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 

Result? The procedure did not end after a timeout. I checked what happens during tracing in the SQL profiler, and it is likely that SQL ends the call in the transaction and should cancel the transaction in timeout.

Note. This test was tested against SQL 2005, but I suspect that the results will be similar in other versions.

Answer 2

As Brendan stated, the client sends an “interrupt” and processing stops. So simple.

However, it is more complex than that ...

Any transactions are not rolled back by default, and locks remain there until the connection is closed. If it returns to the connection pool and is reused, then this is not considered a closure.

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