SQL Server alter database with rollback immediate

sql-server rollback alter-table
Jim Aho picture Jim Aho · Jan 4, 2016 · Viewed 15.3k times · Source

Sometimes when (for example) setting a database offline by executing the following command, an exception will be thrown, because one or more clients is connected:

ALTER DATABASE <dbname> SET OFFLINE

According to this answer one solution is to use with rollback immediate at the end:

ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE

Byt why is actually this disconnecting other clients? It sounds like a very non-obvious way to force disconnections, although it works.

Answer

Tom Page picture Tom Page · Jan 4, 2016

You can't take the database offline while other Users have transactions running on it so you're going to have to disconnect the users one way or another to take the database offline.

If you were being polite you could ask all your users to finish doing whatever they were doing before you took the database offline

but if that's not practical including the option

WITH ROLLBACK IMMEDIATE

Means that all current transactions are Rolled back (the database is reset to the point where the transaction started) . You could also use

WITH ROLLBACK AFTER 60 SECONDS

but I've never had to

If you could take a database offline while there were unfinished transactions were running then the database would be in an unstable state with the change neither made nor cancelled

Related questions

Stored Procedure Transaction

I have never used a Transaction, Commit and Rollback before and now I need to use one. I have checked around online, etc for examples to make sure that I am in fact using this correctly but I am still …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More