Dropping a SQL Server database that is in use

We have been trying to find an effective way of successfully dropping a SQL Server database regardless of whether it is in use or not.

Originally, we wrote scripts to kill all users before proceeding with the database drop. However, we found that users were reconnecting before the execution of the drop command was successful. These users were not ordinary users but rather system connections for monitoring or replication management.

One method to kick users prior to drop is to

USE master

GO

ALTER DATABASE MyDatabaseNameHere SET SINGLE_USER;

However, this does not handle active database replication well.

A much more effective option is to take the database completely offline prior to the drop. In this case SQL Server will gracefully clean up the replication.

USE master

GO

ALTER DATABASE MyDatabaseNameHere SET OFFLINE WITH ROLLBACK IMMEDIATE;

DROP DATABASE MyDatabaseNameHere;

Advertisements
Leave a comment

2 Comments

  1. Damu

     /  June 21, 2011

    Good method to quickly do it. Thanks.

    Reply
  2. Yes this is a good trick Niall,
    I use single_user with rollback immediate command when I want to restore database from snapshot.

    Reply

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: