Termination options for ALTER DATABASE

There are times when a DBA wants to put a database under restricted usage. For example, he wants to run DBCC CHECKDB in Single User mode so that it completes checking the database integrity quickly. Similarly in order to copy the datafiles to a different drive, the database needs to be in OFFLINE mode. The following query is used for this purpose.

ALTER DATABASE databasename SET OFFLINE

This query gets blocked. This happens because there are other users connected to the database when the ALTER DATABASE command was executed. So one can request the users to terminate their sessions and then try this command again. But in a production environment this approach is practically impossible. SQL Server has options which always come to the DBA’s rescue. In this case the ALTER DATABASE command has Termination options which are very useful.

These Termination Options come in to picture when OFFLINE, SINGLE_USER, READ_ONLY and RESTRICTED_USER settings are used in the ALTER DATABASE command. There are three termination options are available. They are

  1. ROLLBACK IMMEDIATE This option ensures that all the other transactions open on the database, except the one executing the ALTER DATABASE command are successfully rolled back.
  2. ROLLBACK AFTER seconds This option makes sure that SQL Server waits for the specified number of seconds before the other open transactions are rolled back
  3. NO_WAIT This option forces SQL Server to check for open connections on the database before issuing the ALTER DATABASE command. If open connections exist the ALTER DATABASE command will fail.

Here is an example of the ALTER DATABASE command with the Termination options.

ALTER DATABASE databasename SET OFFLINE WITH NO_WAIT

When this command executed against a database which has open transactions, immediately the below error message is displayed.

Server: Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database 'databasename'
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

When the ROLLBACK termination options are used, the actual rolling back of the other transactions depend on what those queries were executing. Suppose a query was executing ALTER INDEX command on a huge table. It has to undo whatever action it has done before it is completely rolled back.

On many occasions the ROLLBACK IMMEDIATE option has helped me out.

3 thoughts on “Termination options for ALTER DATABASE

Comments are closed.