Sql Server 2008 R2 Stuck in Single User Mode

sql

sql-server

single-user-mode

maintenance-mode

byNikita Barsukov·Mar 7, 2025

USE master;
-- Hey Siri, kick everyone out and bring back the crowd!
ALTER DATABASE [your_database] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

In SSMS, executing the above T-SQL command terminates all other connections and returns the database to multi-user access mode. Replace [your_database] with your actual database name.

Understanding Single User Mode

Single User Mode is a maintenance mode where only one connection is permitted to the database. Ideal for restoring backups or wrapping up maintenance tasks. Yet, if the doorman forgets to let the crowd in, we have a problem...

The unwanted single party

Detect who's hogging the dance floor by accessing the master database and executing sp_who. If your mystery guest hides here, identify them with the request_session_id from sys.dm_tran_locks.

Before you `ROLLBACK`

WITH ROLLBACK IMMEDIATE is the bouncer here. He will terminate all connections, make sure it doesn't ruin the night by interrupting important transactions.

How to avoid this buzzkill

Plan ahead and keep the party going:

Schedule maintenances during off-peak hours.
Always have a backup plan before hitting the Single User Mode.
Limit permissions to prevent accidental switch to Single User Mode.

The nuclear option: Restart the SQL service

Stuck in Single User Mode and SQL commands not working? Restart the SQL service. This often resolves the issue by resetting transient states (like turning it off and on again, always works, right?).

Navigating tricky scenarios

Getting back to the party isn't always straightforward. Here are a few icebreakers for those sticky situations:

Breaking deadlock chains

Stuck in a deadlock? Shout a bit louder:

-- I'm the DJ, I go first!
SET DEADLOCK_PRIORITY HIGH;

This raises your commands' priority over others and helps break the deadlock.

When the front door doesn't work, try the backdoor

Can't break through the crowd? Use the Dedicated Admin Connection (DAC), the secret passageway for the SQL Server bigwigs.

Last-minute tricks and tips

Some "never-tell" secrets to keep the party vibrant:

Watch out for nested triggers or queued processes - they might be sneakily reconnecting!
Double-check transaction logs for skipping beats (uncommitted transactions).
Remember, nothing spoils a party like a no-entry sign. Ensure your user has the requisite roles and permissions.