How to force/make a MS SQL Database offline

Sometimes there are active connections preventing a database from making offline.

If you have got SMWS open remotely accessing the DB then this could prevent from making it offline.

You can use the following to make it offline.

This will kill all connections and will close database.

ALTER DATABASE database-name SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE

o find connections, use sys.sysprocesses

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

To force disconnections, use ROLLBACK IMMEDIATE

USE masterALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

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