Extreme wait-time when taking a SQL Server database offline

Solution 1:

After some additional searching (new search terms inspired by gbn's answer and u07ch's comment on KMike's answer) I found this, which completed successfully in 2 seconds:

ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE

(Update)

When this still fails with the following error, you can fix it as inspired by this blog post:

ALTER DATABASE failed because a lock could not be placed on database 'dbname' Try again later.

you can run the following command to find out who is keeping a lock on your database:

EXEC sp_who2

And use whatever SPID you find in the following command:

KILL <SPID>

Then run the ALTER DATABASE command again. It should now work.

Solution 2:

There is most likely a connection to the DB from somewhere (a rare example: asynchronous statistic update)

To find connections, use sys.sysprocesses

USE master
SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('MyDB')

To force disconnections, use ROLLBACK IMMEDIATE

USE master
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Solution 3:

Do you have any open SQL Server Management Studio windows that are connected to this DB?

Put it in single user mode, and then try again.

Solution 4:

In my case, after waiting so much for it to finish I had no patience and simply closed management studio. Before exiting, it showed the success message, db is offline. The files were available to rename.

Solution 5:

execute the stored procedure sp_who2

This will allow you to see if there is any blocking locks.. kill their should fix it.