Blocked When Dropping Databases

We have a database that we refresh every night with data from our production system. This is so that we can do a final test with real data when required.

The first step of the job kills all of the users connections, however one of the applications that our developers runs tries to reconnect again whenever it’s session is killed. This means we often get unlucky when trying to drop the database, with an error saying it can’t be dropped because it’s in use.

The work around to this problem was quite simple, we just need to set the database to be single user mode. In the same step of the SQL Agent Job as we drop the database, we now run this bit of code:

ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

This super simple bit of code instructs SQL to rollback any existing queries, don’t accept any new ones, and only allow one connection to SQL at a time. Since I have the connection, I can keep it and nobody else can connect. When I then drop the database I can be sure that there is nobody else connected to it.

CategoriesIT

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