SQL: Reliably Dropping a Database in a T-SQL Script is Too Hard

I often need to create a T-SQL script that reliably drops and recreates a database each time it is executed. A common requirement is that I’m building a script to test come concept and I want to just re-run a script to get back to a known point after cleanign up all that I’ve done before. I could even just want to create a database in a particular way, and I want to make sure it doesn’t already exist.

Unfortunately, reliably dropping a database in a script that you run from SQL Server Management Studio (SSMS) is harder than it looks. And that seems silly.

In the documentation at Microsoft Learn, 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 previously 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 is 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.

You can get around this issue by resorting to dynamic SQL but there’s no way that should be required.

Writing a script to reliably drop and recreate a database is a pretty basic requirement, and I’ve not had that issue with other database engines.

What is really needed to get around this race condition is:

DROP DATABASE WITH ROLLBACK IMMEDIATE;

It just needs to be drop the database and if anyone is connected to it, disconnect them first.

I’ve added this to the SQL Server feedback sites multiple times over the years, but they seem to nuke all these entries as part of some “clean up” every few years. I wish they wouldn’t.

2026-05-28