Home / server / Questions / 764603
Accepted
alifen
Asked: 2016-03-19 05:59:53 +0800 CST

What happens to running queries when stopping a Microsoft SQL Server instance?

  • 772

Consider that a MS SQL Server instance is executing a long-running query the service is stopped via services.msc or - as it actually happening - a system shutdown triggered by a UPS as the server is in a developing country which experiences rolling power blackouts.

When this happens, the service stays in the 'Stopping' state for some time and CPU and disk I/O remains high for that process.

What happens to the queries that are being run? Are they allowed to complete before the service enters the 'Stopped' state or are they cancelled and rolled back?

Ideally, I am looking for an authoritative source (Microsoft documentation) as the answer as I suspect different behavior depending on a factor unknown to me. i.e. sometimes we see queries roll back, sometimes we see them complete. Perhaps the service makes a call as to how far through the transaction it is and if it is quicker to rollback or complete.

If it makes a difference, this is SQL 2012.

Thanks in advance!

sql-server

1 Answers

  1. Best Answer

    It depends on whether you shut the server down with or without NOWAIT.

    From MSDN:

    Unless the WITH NOWAIT 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.

    As for shutting down WITH NOWAIT:

    Optional. Shuts down SQL Server without performing checkpoints in every database. SQL Server exits after attempting to terminate all user processes. When the server restarts, a rollback operation occurs for uncompleted transactions.

    The default is to wait for the currently running transactions to finish (NOWAIT is optional).

    • 2