SQL Server database stuck in single-user mode (error 924): find the session holding it and restore MULTI_USER

#The symptom

A maintenance script, a restore or a CHECKDB repair set a database to SINGLE_USER and either failed or never switched it back. Now every connection to that database fails:

Msg 924, Level 14, State 1
Database 'SalesDB' is already open and can only have one user at a time.

When you try to fix it, you may get this instead:

Msg 5064, Level 16, State 1
Changes to the state or options of database 'SalesDB' cannot be made at this time.
The database is in single-user mode, and a user is currently connected to it.

Both mean the same thing: one session already owns the single allowed connection, and it is not yours. The job is to find that session, end it, and switch the database back in the same move.

#Step 1: Connect without touching the database

Do not open SSMS with the stuck database as your default database, and do not expand it in Object Explorer. Object Explorer and IntelliSense open their own connections, and one of them can grab the slot the moment it frees up.

Use sqlcmd and land in master:

sqlcmd -S PRODSQL01 -E -d master

If your login's default database is the stuck one, the -d master switch is what gets you in. In SSMS, set Options > Connection Properties > Connect to database to master and close Object Explorer for that server.

#Step 2: Confirm the state

SELECT name, user_access_desc, state_desc, is_auto_update_stats_async_on
FROM sys.databases
WHERE name = N'SalesDB';

user_access_desc is SINGLE_USER, RESTRICTED_USER or MULTI_USER. If it says RESTRICTED_USER, only members of db_owner, dbcreator and sysadmin can connect. The fix is the same SET MULTI_USER below.

#Step 3: Find the session that holds it

sys.dm_exec_sessions has a database_id column, so this shows anyone whose current database is the stuck one:

SELECT s.session_id, s.login_name, s.host_name, s.program_name,
       s.status, s.is_user_process, s.last_request_start_time
FROM sys.dm_exec_sessions AS s
WHERE s.database_id = DB_ID(N'SalesDB');

A session can also hold the database lock without having it as its current database (for example a query that uses three-part names). Check the lock manager too:

SELECT l.request_session_id, l.request_mode, l.request_status,
       s.login_name, s.host_name, s.program_name
FROM sys.dm_tran_locks AS l
LEFT JOIN sys.dm_exec_sessions AS s
       ON s.session_id = l.request_session_id
WHERE l.resource_type = 'DATABASE'
  AND l.resource_database_id = DB_ID(N'SalesDB');

Read program_name before you kill anything. Typical owners:

• Microsoft SQL Server Management Studio or ...IntelliSense: someone's open query window or Object Explorer.
• SQLAgent - TSQL JobStep (Job 0x...): an Agent job that ran in the window.
• An application name: the app reconnected as soon as the database became reachable.
• is_user_process = 0: a background task. With AUTO_UPDATE_STATISTICS_ASYNC on, the async statistics thread opens a connection to the database and can hold the single slot.

#Step 4: Kill the session and switch back in one batch

Run the kill and the ALTER together, from master, so nothing can slip in between:

USE master;
SET DEADLOCK_PRIORITY HIGH;

KILL 87;   -- the session_id from step 3

ALTER DATABASE [SalesDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Warning

KILL rolls back that session's open transaction. If it was a long data change, the rollback can take as long as the change did, and the database stays unavailable until it finishes. Check progress with KILL 87 WITH STATUSONLY; and do not restart the instance to "speed it up"; recovery will just redo the rollback.

If the session keeps reappearing (an app or job retrying), loop until the ALTER wins:

USE master;
SET DEADLOCK_PRIORITY HIGH;

DECLARE @sql nvarchar(max);
DECLARE @tries int = 0;

WHILE EXISTS (SELECT 1 FROM sys.databases
              WHERE name = N'SalesDB' AND user_access_desc <> 'MULTI_USER')
  AND @tries < 20
BEGIN
    SET @sql = N'';
    SELECT @sql += N'KILL ' + CAST(session_id AS nvarchar(10)) + N'; '
    FROM sys.dm_exec_sessions
    WHERE database_id = DB_ID(N'SalesDB')
      AND session_id <> @@SPID
      AND is_user_process = 1;

    EXEC (@sql);

    BEGIN TRY
        ALTER DATABASE [SalesDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE();
        WAITFOR DELAY '00:00:01';
    END CATCH;

    SET @tries += 1;
END;

If a background session (is_user_process = 0) is the holder, it is usually the async statistics update. It normally finishes on its own within seconds to minutes; you can also find it with sys.dm_exec_requests (command shows a statistics-related task) and kill it, then run the ALTER.

#Stop the reconnecting client first

If an application pool or service reconnects faster than you can alter, stop it at the source for a minute: stop the app service, disable the Agent job, or disable the login temporarily:

ALTER LOGIN [app_sales] DISABLE;
-- kill + ALTER DATABASE ... SET MULTI_USER
ALTER LOGIN [app_sales] ENABLE;

Caution

Disabling a shared login takes down every database that application uses. Agree it with the app owner first, and re-enable it in the same change window.

#Step 5: Verify

SELECT name, user_access_desc, state_desc
FROM sys.databases
WHERE name = N'SalesDB';

MULTI_USER and ONLINE means you are done. Ask the app team to confirm connections, and re-enable anything you stopped.

#Availability groups and Managed Instance

• A database that is part of an Always On availability group cannot be set to SINGLE_USER. If a script needs exclusive access, remove the database from the AG first and plan to re-join it. Check the ALTER DATABASE SET options documentation for your version before relying on other access modes on AG databases.
• On Azure SQL Managed Instance the same DMVs and KILL work for user sessions. If only a system session holds the database and it does not clear, open a support case rather than fighting it.

#Prevent it next time

Most stuck single-user databases come from scripts that switch to SINGLE_USER and then lose the connection or fail before switching back. Write maintenance scripts so the same session takes the slot and gives it back:

USE master;
ALTER DATABASE [SalesDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

BEGIN TRY
    USE [SalesDB];     -- take the slot immediately in this session
    -- maintenance work here, for example:
    -- DBCC CHECKDB (N'SalesDB', REPAIR_REBUILD);
    USE master;
    ALTER DATABASE [SalesDB] SET MULTI_USER;
END TRY
BEGIN CATCH
    USE master;
    ALTER DATABASE [SalesDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
    THROW;
END CATCH;

A few habits that help:

• Run these scripts from sqlcmd or an Agent job connected to master, not from an SSMS window with Object Explorer open on the database.
• Consider RESTRICTED_USER instead of SINGLE_USER when you only need to keep the application out. DBAs can still connect, and the "someone took my slot" problem goes away.
• Turn AUTO_UPDATE_STATISTICS_ASYNC off before setting SINGLE_USER on databases that use it, and back on afterwards.
• Add a daily check to your monitoring that alerts on any database where user_access_desc <> 'MULTI_USER' outside a change window:

SELECT name, user_access_desc
FROM sys.databases
WHERE user_access_desc <> 'MULTI_USER';