SQL Server 2008 R2 – Service Shutdown and Running Stored Procedures

sql server

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:

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 ?

Best Answer

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:

Disabling logins (except for members of the sysadmin and serveradmin fixed server roles).
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.
Inserting a checkpoint in every database.
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.

Related Solutions

SQL Azure – Fix Slow Insert Through Linked Server

I insert into this table small amount of data just about 1000 rows and it takes more than 4 minutes

Remote data modifications through a linked server use the sp_cursor model. The effect is similar to issuing 1000 separate single inserts (one for each row). If a round trip takes 250ms, 1000 such trips will take 4 minutes and 10 seconds. Using a bulk loading method such as bcp or SSIS will generally be more efficient (unless the number of rows to be inserted is tiny).

Another alternative for ad-hoc needs is to build a string containing a single INSERT statement with multiple rows in the VALUES clause. The statement is then used with EXECUTE AT, for example:

-- Note local-to-azure table name used, not four-part syntax
DECLARE @sql varchar(max) = 'INSERT dbo.Test VALUES ';

-- Build a list of 1000 numbers to insert    
SELECT @sql += '(' + CONVERT(varchar(11), n) + '),'
FROM dbo.Numbers AS N
WHERE n BETWEEN 1 AND 1000;

-- Remove trailing comma
SET @sql = LEFT(@sql, LEN(@sql) - 1);

-- For debugging
PRINT @sql;

-- Execute the finished statement at the remote server;
EXECUTE (@sql) AT AZURE;

The constructed insert statement executes at the Azure database, so a local name is used for the table (note: building a dynamic insert statement using a four-part target name buys you nothing).

Note that EXECUTE ... AT requires RPC and RPC OUT enabled in the linked server options.

The VALUES clause for a plan INSERT statement is limited to 1000 elements. There are ways around that (a VALUES clause in a CTE does not have that restriction). You could also choose to build 1000-row batches or build single-row insert statements, but if the there is that much data on a regular basis, you would probably be better off using one of the bulk loading methods instead.

SQL Server – Differentiate SQL Server Express from Server Installation

The MSSQLSERVER name in the services does not mean anything other than SQL Server (any edition) was installed as the default (unnamed) instance. It does NOT give you any information as to what version or edition it is! This can be SQL Server Express!

You need to connect to that database instance and run

SELECT @@VERSION

to find out what exact version/edition it is.

Having the two SQL Server service entries just means you have two instances - which edition each instance is cannot be determined from their names alone. OK - SQLEXPRESS is the default name used for SQL Server Express - but you could install a Standard or Enterprise edition with that instance name - just for fun.

Best Answer

Related Solutions

SQL Azure – Fix Slow Insert Through Linked Server

SQL Server – Differentiate SQL Server Express from Server Installation

Related Question