Reliably Dropping a Database in a T-SQL Script is Too Hard
Recently I’ve been working on a system where I had to create a test script that reliably recreated a database each time it was executed. I’ve done this many times before and thought I knew how but I was wrong. It turns out that reliably dropping a database in a script that you run from SQL Server Management Studio (SSMS) is harder than it looks. (It’s also harder than it should be).
In Books Online (BOL), the article for DROP DATABASE says :
“You cannot drop a database currently being used. This means open for reading or writing by any user. To remove users from the database, use ALTER DATABASE to set the database to SINGLE_USER.”
Based on this, I had been using the following style of script to drop and recreate a database:
USE master; GO
IF EXISTS(SELECT 1 FROM sys.databases WHERE name = N’Blah’) BEGIN ALTER DATABASE Blah SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE Blah; END; GO
CREATE DATABASE Blah ON
…
However, what I’ve found is that when I execute this from SSMS, it doesn’t always work. Fairly randomly (and hard to reproduce), it fails with an error message telling me that the DROP failed because the database was in use. But isn’t that the whole purpose of setting it to single user?
The problem seems to be that although the database gets set to single user, another connection could be made to it before the DROP DATABASE statement occurs. Setting the database to SINGLE_USER isn’t enough as the current database context for the script is the master database, not the database in question.
A number of users and fellow MVPs have told me that they’ve experienced the same issue. What we suspect is causing this is the Intellisense system within SSMS connecting to the database to check the syntax of objects later in the same script. The problem only really seems to happen in SSMS but it could happen elsewhere.
A great suggestion from Rob Farley was to set the database OFFLINE instead of SINGLE_USER. While that would stop the other connection, the problem is that when you DROP a database that is OFFLINE, the files for the database don’t get deleted. You’d then need to have some additional script (nasty) to delete the files as well after the database was dropped.
What is really needed to get around this race condition is:
DROP DATABASE WITH ROLLBACK IMMEDIATE;
If you agree, I’ve created a Connect item here that you can vote on:
(Update 8 Sep 2019): As all the Connect items got nuked again, that link doesn’t work anymore. I did just find it in the new UserVoice system under a different name here: https://feedback.azure.com/forums/908035-sql-server/suggestions/32906317-cannot-reliably-drop-a-database-in-t-sql-script
2014-01-06