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:
[code language=”sql”]ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;[/code]
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.