SQL Server: The Database Is in Single User Mode (Fix)

You try to connect to a database and get:

The database is in single user mode.

Or you attempt to switch it back to multi-user and receive:

ALTER DATABASE failed because the database is in use.

This usually happens after maintenance, a restore, or when someone has set the database to SINGLE_USER and not switched it back.

In single-user mode, only one connection is allowed at a time. If anything else connects first — including SQL Server Agent, an application, or even Object Explorer in SSMS — your session will be blocked.

---

Why This Happens

A database can be placed into single-user mode using:

ALTER DATABASE [database_name]
SET SINGLE_USER;

This is commonly done for:

• Restores
• Emergency maintenance
• Schema changes
• Repair operations

If the database is not switched back to MULTI_USER, it remains restricted.

Even worse, background connections (like SQL Agent jobs or monitoring tools) can immediately grab the single available connection before you do.

---

The Fix

The cleanest way to switch the database back is:

ALTER DATABASE [database_name]
SET MULTI_USER
WITH ROLLBACK IMMEDIATE;

WITH ROLLBACK IMMEDIATE forces any existing session to disconnect and rolls back incomplete transactions, allowing the mode change to complete immediately.

Without it, you may see:

ALTER DATABASE failed because the database is in use.

---

If You Still Can’t Get In

If connections continue grabbing the database instantly:

1. Stop SQL Server Agent temporarily.
2. Close Object Explorer connections.
3. Kill active sessions manually:

SELECT session_id
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('[database_name]');

Then:

KILL <session_id>;

Once no other connections exist, run the SET MULTI_USER WITH ROLLBACK IMMEDIATE command again.

---

Confirm the Current Mode

To check whether a database is in single-user mode:

SELECT name, user_access_desc
FROM sys.databases
WHERE name = '[database_name]';

Values will be:

• MULTI_USER
• SINGLE_USER
• RESTRICTED_USER

---

Final Thoughts

If a database is stuck in single-user mode, it’s almost always just an access issue — not corruption.

Use SET MULTI_USER WITH ROLLBACK IMMEDIATE, ensure no other sessions are connected, and the database will return to normal access.