Keeping the Single User Connection

A reader was having issues with a script. They ran their code from PoSh (Invoke-Sqlcmd), setting single user and then trying to rename a database and its files. However, occasionally, they’d lose the single user connection to another user. Frustrating, but it wasn’t a PoSh issue.

The ALTER DATABASE commands don’t really specify how SINGLE_USER works. What happens is that if you set single user, the first user gets the connection, whether that’s sysadmin, dbo, or a regular user. This means that when you set a database to single user mode, you need to be one of the users in the database.

The change to single used is blocked, until all other connections disconnect. One way to ensure this happens quickly is to add the WITH ROLLBACK IMMEDIATE clause, which disconnects users.

The original poster’s issue was that while they used the clause, their connection was in the master database. This meant that another user could potentially connect to the database, grab a shared lock, and prevent renaming files.

If your connection is in the user database, then when you issue the ALTER DATABASE WITH ROLLBACK IMMEDIATE and disconnect users, then you have the only connection. This should prevent any issues.

Quick Demo

Let’s see this in a simple way. I’ve got a demo database that I use, and I’ll open three connections. The first will be as a sysadmin here:

2017-10-24 09_54_17-Connect to Database Engine

and then the other windows I’ll switch the connection to a non-privileged user.

2017-10-24 09_55_17-SQLQuery20.sql - Microsoft SQL Server Management Studio

At this point, I have a few connections to my database.

2017-10-24 10_00_48-SQLQuery19.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (56))_ - Microsoft

Let me now reset the database option.

2017-10-24 10_01_30-SQLQuery19.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (56))_ - Microsoft

Everything works here, let’s check the next session. If I switch to another tab, I’ll run a query. After a second or two, I get this:

2017-10-24 10_02_29-SQLQuery21.sql - (local)_SQL2016.EncryptionDemo (AEUser (61)) Executing..._ - Mi

SQL Server has disconnected my session. If I try to reconnect (this login has EncryptionDemo as the default), I get this:

2017-10-24 10_17_04-SQLQuery22.sql - Microsoft SQL Server Management Studio

If you need to perform maintenance, and want to ensure you have the connection, change context to the database before setting single user mode.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.