Extreme wait-time when taking a SQL Server database offline

When it takes long time to make a database offline try with following sql.

ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE

If it does not help, kill the session.

First find out the spid using following command.

EXEC sp_who2

KILL <SPID>

Find active connections and kill

Find active connections

SELECT DB_NAME(dbid) as DBName, dbid,loginame as LoginName,CAST(SPID AS VARCHAR(4)) AS spid
FROM     sys.sysprocesses WHERE dbid>10
ORDER BY dbid           

You will find spid. Then kill the connection by spid. Do not kill yourself.

KILL 61