Taking a Database Offline in SQL Server Management Studio

To take a database offline in SSMS, simply right-click the database, select Tasks, Take Offline, and you’re done.

Except, if it does not work because there are still open connection to the database.

In this case, you need to find out the connections’ SPID

SELECT SPId FROM master..SysProcesses 
WHERE DBId = DB_ID('MyDatabase') AND SPId <> @@SPID

and kill each of the connections by executing

KILL [spid from query above]

Finally, you can take the database offline from its context menu, or, as you already work in a SSMS Query window, simply type


I just found this script today, so I’m afraid I cannot give you any sources of this magic wisdom.

I did however find an older post here dealing with SSMS Express Edition.

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: