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.

So, my DROP fails.

The Intellisense connection is a pooled connection, and stays around for at least a few minutes after you last use it. So, even if you seem to have nothing connected to the DB, it can still be connected.

To try to help with this, the SSMS team have a Close Idle Connections option. It closes any idle pooled connections.

The documentation says This state can prevent actions from being taken that require all connections to be closed, such as dropping or altering a database.

It’s a pity that in the name of the menu option, it doesn’t mention that it only affects pooled connections. Your normal query window connections are not pooled and are unaffected by this option.

I do wish there was an option to close any connection that has been idle for more than a certain number (e.g., 5) of minutes. But that’s not what it does.

There is one other strange thing that was added to T-SQL a while back. While the team was adding all the DROP … IF EXISTS options, they added one for databases. It’s not very useful. It also fails if anyone is connected.

What’s needed?

What’s really needed is a way to combine the dropping with the disconnecting such as:

DROP DATABASE IF EXISTS somedb WITH ROLLBACK IMMEDIATE;

Then we wouldn’t need most of these messy options.

2025-10-14