Interview Question of the Week #049 – Taking Database Offline

Question: Can I take a database offline immediately, or allow a short wait first? Yes. The termination clause controls when SQL Server forces incomplete transactions to roll back while changing the database state.

The original interview conversation made the distinction memorable. A DBA had a command ready to take a database offline. Then the manager asked: could the people already working have another 30 seconds?

SELECT name,state_desc FROM sys.databases WHERE name=N'mydb';
-- Maintenance templates, not commands to run on an arbitrary database:
-- USE master;
-- ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK IMMEDIATE;
-- Or allow a grace period before forcing incomplete transactions back:
-- ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK AFTER 30 SECONDS;
-- Bring it back when the maintenance is complete:
-- ALTER DATABASE [mydb] SET ONLINE;

ROLLBACK IMMEDIATE does not grant that grace period. It starts forcing unfinished work to roll back and disconnecting other users so the state change can obtain the access it needs.

ROLLBACK AFTER 30 SECONDS allows the specified period before forcing incomplete transactions to roll back. A transaction that completes successfully before termination is not undone just because the database subsequently goes offline.

Thirty seconds is not a promise about the entire operation

The command can finish sooner if it does not need to wait, and rollback itself can take longer. This option is not an application contract that every request receives exactly 30 more seconds of work.

For planned maintenance, stop incoming application work first, notify the users, and decide whether unfinished transactions should be allowed to complete or be cancelled. A bare SET OFFLINE can wait indefinitely on conflicting access. WITH NO_WAIT instead fails when the change cannot complete immediately.

Run the command from a connection in master, not from the database being taken offline, and verify the intended database name. OFFLINE preserves the database files; SET ONLINE returns the database to service after the maintenance.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.