SQL Server 2008 R2 - Service shutdown - what with running stored procedures?

Question

I would like understand below behavior of SQL Server:

SQL Version:

Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) 
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Let's assume that we have stored procedure as the following (some code omitted for brevity):

CREATE PROCEDURE Test
AS
BEGIN TRY
    INSERT INTO test_log ('Stage 1. PROC started')

    ...

    EXEC (@ScheduledProcedure) -- Execution of this takes 8-10 mins
    INSERT INTO test_log ('Stage 2. Scheduled Procedure completed')
END TRY
BEGIN CATCH     

    ...

END CATCH;

INSERT INTO test_log ('Stage 3. PROC Completed')

I have done some testing to check what happens with my procedure when I shut SQL Server down when my procedure is still running and here are the results:

1. When I go to the services.msc and stop the SQL Server, the service will be in 'Stopping' state until @ScheduledProcedure is completed (8-10 mins).

2. When I shut the OS down it seems that SQL Service cancels stored procedure. I think that it is the case, because Stage 2 and 3 messages are not available in test_log table.

Can someone try to explain this SQL Server behavior ?

Answer 1

When I go to the services.msc and stop the SQL Server, the service will be in 'Stopping' state until @ScheduledProcedure is completed (8-10 mins).

What you are seeing is the default behavior when you shut down SQL Server. If you do not want want to wait you can use WITH NOWAIT switch but when SQL server starts recovery time will be longer as transactions has to roll forward and roll back.

Details here.

Unless the WITHNOWAIT option is used, SHUTDOWN shuts down SQL Server by:

1. Disabling logins (except for members of the sysadmin and serveradmin fixed server roles).
2. Waiting for currently running Transact-SQL statements or stored procedures to finish. To display a list of all active processes and locks, run sp_who and sp_lock, respectively.
3. Inserting a checkpoint in every database.
4. Using the SHUTDOWN statement minimizes the amount of automatic recovery work needed when members of the sysadmin fixed server role restart SQL Server.

When I shut the OS down it seems that SQL Service cancels stored procedure. I think that it is the case, because Stage 2 and 3 messages are not available in test_log table.

In this case SQL Server has no control as operating system issue a shut down process command and that is why you are seeing immediate shutdown.Transactions that were completed and written to the transaction log but not yet applied to the database, will be applied to the database. Open, unfinished, transactions are rolled back, again to ensure the database is in a consistent state. Details about roll forward and roll back here.

Different ways to shut down SQL Server.

Answer 2

When you stop the SQL service SQL Server will wait for sp to complete.

When you shut down Windows SQL Server is going to perform checkpoints and after restart rollback uncommitted transactions.