SQL Server – Take database offline hangs

With thanks to following and other web searches:

http://pianaro.wordpress.com/2012/11/30/sql-server-database-take-offline-hangs/

Scenario

New SQL Server on a new Azure VM, with a few new issues to learn from. I attempted to take a database offline but it hung.

Solution

USE master

SELECT resource_type, resource_associated_entity_id,
    request_status, request_mode,request_session_id,
    resource_description
    FROM sys.dm_tran_locks

--Find troublesome session and kill it.
--You cannot kill your own process, so you may need to start another session
KILL [SPID]

To take the database offline without waiting try this:

alter database DOTNETNUKE_EBC set offline with rollback immediate

To bring database online:

alter database DOTNETNUKE_EBC set online

End