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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: