SQL Server alter database with rollback immediate

Question

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

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

Answer 1

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