SSMS Tips and Tricks 5-9: Closing idle connections
One challenge that I find with T-SQL is that there’s no 100% reliable way to drop a database.
I wish I was joking.
If you execute DROP DATABASE, the command will fail if anyone is connected to the database. The way that we normally drop databases is as follows:
USE master;
GO
IF EXISTS (SELECT 1 FROM sys.databases AS d WHERE d.[name] = N'somedb')
BEGIN
ALTER DATABASE somedb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE somedb;
END;
That mostly works, but the problem is that I need to execute the command from the master database. That means that when I set the database to single user, I don’t know that I’m the single user. What I’ve seen happen sometimes, is that the Intellisense system in SSMS is reading further down my script, where I’m perhaps recreating the database, and it’s maintaining a connection to the DB.
2025-10-14