2

What happens to a SQL Server stored procedure that was initiated by a client C# program's call to the SqlCommand.ExecuteNonQuery method after the CommandTimeout expires?

  • Does SQL Server continue running the stored procedure to completion (even though the client gave up on waiting for the result)?
  • Does SQL Server stop executing the sproc, but any changes made by the stored procedure up to that point remain?
  • Does SQL Server stop executing the sproc, and roll back any changes that were made?
  • It depends? (If so, on what?)

To take a specific example: Consider the following (simplified) snippet of code, which is run by a scheduler once per evening, and calls a particular stored procedure, which cleans up a large quantity of obsolete database records from a very large table:

SqlConnection connection = new SqlConnection(_myConnectionStringToSqlServer);
using (SqlCommand command = Connection.CreateCommand())
{
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "MyLongRunningCleanupOldRecordsStoredProcedure";
    command.CommandTimeout = 600;  // 10 minutes

    command.ExecuteNonQuery();
}

If the SQL Server instance is still only midway through running the MyLongRunningCleanupOldRecordsStoredProcedure sproc after the configured 10 minute timeout period, and the client program times out and throws an exception, what is the behavior of the database at that point? (Does it proceed to finish running the stored procedure and clean up the remainder of the records? Are any of the records already cleaned up rolled back?)

Jon Schneider
  • 25,758
  • 23
  • 142
  • 170
  • https://stackoverflow.com/questions/16589601/what-does-sql-server-do-with-a-timed-out-request ? – Lanorkin Jan 24 '20 at 19:32
  • @Lanorkin, Yep, this question is a duplicate of that one, or near enough! I've voted to close this one. Thanks for the link! – Jon Schneider Jan 24 '20 at 19:37

0 Answers0