What does SQL Server do with a timed out request?

Question

Suppose that I use C# to run a long running SQL Server stored procedure (lets say 30 minutes). Further suppose that I put a 1 hour timeout period on the query in C# such that if for whatever reason this SP takes longer than expected, I don't end up monopolizing the DB. Lastly, suppose that this stored procedure has a try/catch block in it to catch errors and do some clean-up should any steps inside it fail.

Some code (C#):

using (SqlCommand comm = new SqlCommand("longrunningstoredproc"))
{
    comm.Connection = conn;
    comm.CommandType = CommandType.StoredProcedure;
    comm.CommandTimeout = 3600;
    comm.ExecuteNonQuery();
}
/* Note: no transaction is used here, the transactions are inside the stored proc itself. */

T-SQL (basically amounts to the following):

BEGIN TRY
   -- initiailize by inserting some rows into a working table somewhere
   BEGIN TRANS
     -- do long running work
   COMMIT TRANS
   BEGIN TRANS
     -- do long running work
   COMMIT TRANS
   BEGIN TRANS
     -- do long running work
   COMMIT TRANS
   BEGIN TRANS
     -- do long running work
   COMMIT TRANS
   BEGIN TRANS
     -- do long running work
   COMMIT TRANS
   -- etc.

   -- remove the rows from the working table (and set another data point to success)
END TRY
BEGIN CATCH
   -- remove the rows from the working table (but don't set the other data point to success)
END CATCH

My question is, what will SQL Server do with the query when the command times out from the C# side? Will it invoke the catch block of the SP, or will it just cut it off altogether such that I would need to perform the clean-up in C# code?

Answer 1

The timeout is enforced by ADO.NET. SQL Server does not know such a thing as a command timeout. The .NET client will send an "attention" TDS command. You can observe this behavior with SQL Profiler because it has an "attention" event.

When SQL Server receives the cancellation it will cancel the currently running query (just like SSMS does when you press the stop button). It will abort the batch (just like in SSMS). This means that no catch code can run. The connection will stay alive.

In my experience the transaction will be rolled back immediately. I don't think this is guaranteed though.

TL;DR: A timeout in ADO.NET behaves the same as if you had pressed stop in SSMS (or called SqlCommand.Cancel).

Here is reference for this: https://techcommunity.microsoft.com/t5/sql-server-support/how-it-works-attention-attention-or-should-i-say-cancel-the/ba-p/315511

Answer 2

The timeout is something that happens on the connection, not the running query.

This means that your BEGIN CATCH will not execute in the event of a timeout, as the query has no idea about it.

Write your cleanup in C#, in a catch(SqlException ex) block (testing for a timeout).