I would like understand below behavior of SQL Server:
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:
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).
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 ?