About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.
SQL Server error 924 appears when a database is in single-user mode and its one permitted connection is already in use. Microsoft lists it with severity 14 and the message text Database '%.*ls' is already open and can only have one user at a time., where the database name replaces the placeholder. The database stays in single-user mode after the session that set it has gone, so the fix is to find who holds the single connection and then set the database back to MULTI_USER.
What the error means
A database in SINGLE_USER mode accepts one connection at a time. Microsoft documents it this way: "The database remains in single-user mode even after the user that set the option is disconnected. At that point, a different user, but only one, can connect to the database." Whoever connects first after the change takes that seat, and another connection attempt while it is taken fails with error 924.
The error list marks 924 as not event-logged, so do not expect a line in the SQL Server error log. The message reaches the client that tried to connect. Microsoft's page for setting single-user mode also says that when the mode is set, other connections to the database "will be closed without warning", and that a background statistics thread can take the seat.
Version scope: Microsoft's error list shows the 924 row, with the same text and severity 14, in every one of its version tables. This applies to SQL Server on Windows. Azure SQL and Linux instances are not covered here.
Likely causes
| Cause | What you see | How to confirm |
|---|---|---|
| A maintenance script set SINGLE_USER and stopped before setting MULTI_USER | user_access_desc is SINGLE_USER and nothing is running against the database |
Query sys.databases, then check the SQL Agent job history for a failed step |
| Another application or person took the one connection | One session sits in the database, for example under a service account | Query sys.dm_exec_sessions for sessions whose database is the affected one |
| The background thread for asynchronous statistics took the connection | The setting is_auto_update_stats_async_on is 1 and no user session is listed |
Check the column in sys.databases, then sys.dm_exec_background_job_queue |
| Log shipping: a restore job set single-user mode and another connection arrived first (a question asker's report on Microsoft Q&A; Microsoft staff's answer covers only connected users blocking restores) | A secondary database is stuck in single-user mode and restores fail | Match the database to the log shipping restore job and its history |
| Your own earlier query window or tool | The only session belongs to your login and host | login_name, host_name and program_name in the session list |
Prove the cause before changing anything
These checks only read. Connect to the master database, as Microsoft's own examples do, so that your own session does not start in the affected database.
1. Confirm the mode. Replace SalesDb with your database name.
SELECT name, user_access_desc, state_desc, is_auto_update_stats_async_on
FROM sys.databases
WHERE name = N'SalesDb';
Microsoft documents user_access as 0 for MULTI_USER, 1 for SINGLE_USER and 2 for RESTRICTED_USER. A user_access_desc of SINGLE_USER confirms the mode. If it already says MULTI_USER, the problem has cleared, or you are looking at a different instance or database name.
2. List the sessions in that database.
SELECT s.session_id, s.login_name, s.host_name, s.program_name, s.status,
s.login_time, s.last_request_start_time, s.last_request_end_time,
s.open_transaction_count, s.is_user_process
FROM sys.dm_exec_sessions AS s
JOIN sys.databases AS d ON d.database_id = s.database_id
WHERE d.name = N'SalesDb';
Microsoft documents database_id as the current database of each session. The view needs VIEW SERVER STATE on SQL Server 2019 and earlier, and VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later. Without it you see only your own session.
How to read the result:
- One user session with a recent
last_request_end_timeandopen_transaction_countof 0: an application is connected and idle. Find its owner before you remove it. - One session with
open_transaction_countabove 0: removing it rolls that work back. - No session at all, but
is_auto_update_stats_async_onis 1: suspect the statistics thread.
3. Look at the statistics queue if step 2 shows nothing.
SELECT * FROM sys.dm_exec_background_job_queue;
Microsoft's ALTER DATABASE page names this view for finding active asynchronous statistics jobs.
Fix
Pick the row that matches your proof. Start with the least disruptive.
A. A script left SINGLE_USER and no session is attached. Set the mode back, using the master database as Microsoft's example does.
USE master;
GO
ALTER DATABASE [SalesDb] SET MULTI_USER;
GO
Risk: this admits every login that has access. If a restore or repair that needs exclusive access is still in progress, letting users in can interfere with it. To back out, set the database to SINGLE_USER again, which closes other connections.
B. Another session holds the connection. In order of impact:
Ask the owner shown by
login_name,host_nameandprogram_name, and stop that application or service in the usual way for it. Then run the statement from fix A.Risk: stopping an application interrupts its users and any work in flight. Back out by starting it again, but only after fix A, because it can take the single connection again.
Run the statement with a termination clause. Microsoft documents that without a termination clause the statement "waits indefinitely if there's any lock on the database".
ALTER DATABASE [SalesDb] SET MULTI_USER WITH ROLLBACK AFTER 60 SECONDS;
Disruptive step: ROLLBACK AFTER rolls back incomplete transactions after the stated seconds, and ROLLBACK IMMEDIATE does it at once. Connections are dropped. Rolled-back work cannot be recovered, and there is no way back. The application may reconnect and take the seat again, so stop it first if it retries on its own.
- End the session by number, only when it belongs to nothing important.
Replace 87 with the session_id from step 2. To end the session:
KILL 87;
To read the rollback progress afterwards without ending anything else:
KILL 87 WITH STATUSONLY;
Disruptive step: KILL ends the connection and rolls back its open transactions. Microsoft warns that a long transaction can take a long time to roll back, and that WITH STATUSONLY reports progress without ending anything. You cannot kill your own session or system processes, and the session number can be reused after the rollback finishes. The change cannot be reversed. It needs the ALTER ANY CONNECTION permission.
C. The statistics thread took the connection. Microsoft says to set AUTO_UPDATE_STATISTICS_ASYNC to OFF, check sys.dm_exec_background_job_queue, and either let active jobs finish or end them with KILL STATS JOB.
ALTER DATABASE [SalesDb] SET AUTO_UPDATE_STATISTICS_ASYNC OFF;
Risk: this changes a database option that stays in place after the fix. To back out, set it to ON again.
If sys.dm_exec_background_job_queue shows an active job you do not want to wait for, Microsoft's KILL STATS JOB page gives the syntax. The job_id is the value from that view and is unrelated to session_id; Microsoft's example uses 53.
KILL STATS JOB 53;
Disruptive step: this ends that statistics update job before it finishes. The job cannot be resumed; there is no way back for that run. It needs membership in the sysadmin or processadmin role. Check the job_id against the view first, because a wrong number ends the wrong job.
D. Log shipping. The only Microsoft statement on this case is a Microsoft Q&A answer from an account labelled Microsoft External Staff, because no Learn page covers it. It says a restore job cannot restore logs while users are connected, that the behaviour is by design, and that log backups accumulate until no user connections remain when the option to disconnect users is not selected. Stop the extra connection, then let the next restore job run. If the database stays single-user, use fix A or B.
E. Your own session. Close the window or use that session to run fix A.
Confirm the fix
Run step 1 again. user_access_desc should read MULTI_USER. Then connect as the application's own account, starting in the affected database, and run a harmless query. Run step 2 again and confirm the application's sessions are back and none belongs to a maintenance tool that should have finished.
Example (illustrative): on sql01, a nightly job sets SalesDb to SINGLE_USER at 02:00 for an index rebuild and fails at 02:20 before its final MULTI_USER step. At 02:21 a reporting service, CONTOSO\svc-reports, connects and holds the one connection. At 06:00 the order system reports error 924. By 07:40 the database has been in single-user mode for 340 minutes (07:40 minus 02:00 is 5 hours 40 minutes), and the order system has been failing for 100 minutes. Step 2 shows session 87 with program_name ReportSync, open_transaction_count 0 and last_request_end_time 07:35. The administrator asks the reporting owner to stop the service, then runs fix A.
What to watch afterwards
- Add a check for any database whose
user_access_descis not MULTI_USER and alert on it. It is a plain read ofsys.databases. - Make scripts that set SINGLE_USER finish with SET MULTI_USER in every exit path. Microsoft's own example ends with it.
- For log shipping secondaries, watch restore job history for failures that follow a connection from an application.
- Keep AUTO_UPDATE_STATISTICS_ASYNC in mind before any planned single-user work.
Read next
Related articles you may want next.
- Backup & RecoverySQL Server restore error 3101: exclusive access could not be obtained because the database is in useRESTORE fails with error 3101 because sessions still use the database. Find them, clear them safely, restore, and avoid the data-loss traps.Read article
- Backup & RecoverySQL Server database stuck in 'Restoring...': finish the restore or recover it safelyWhy a SQL Server database sits in Restoring, how to tell a live restore from NORECOVERY or a standby copy, and how to recover without losing logs.Read article
- TroubleshootingSQL Requests Are Waiting: Find the Head Blocker Before Ending SessionsBuild a time-correlated blocking chain and identify the transaction owner before deciding on intervention.Read article
Risks and limits
These commands have not been run or lab-tested. The error list shows the 924 row in every version table. Microsoft's pages used here do not describe a stuck single-user state in cluster, mirroring or availability group settings, so those cases need their own checks.
ROLLBACK IMMEDIATE, ROLLBACK AFTER and KILL discard uncommitted work. Choose them only after the session owner has agreed or the work is known to be disposable. A rollback can itself take long, and the database is not released until it finishes.
Sources
- Database Engine events and errors 0 to 999 (error 924) — Microsoft Learn, page dated 2026-10-27, accessed 18 November 2026
- Set a database to single-user mode — Microsoft Learn, page dated 2025-02-27, accessed 18 November 2026
- ALTER DATABASE SET options (Transact-SQL) — Microsoft Learn, page dated 2026-07-21, accessed 18 November 2026
- sys.databases (Transact-SQL) — Microsoft Learn, page dated 2026-09-21, accessed 18 November 2026
- sys.dm_exec_sessions (Transact-SQL) — Microsoft Learn, page dated 2026-11-02, accessed 18 November 2026
- KILL (Transact-SQL) — Microsoft Learn, page dated 2026-09-21, accessed 18 November 2026
- KILL STATS JOB (Transact-SQL) — Microsoft Learn, page dated 2026-08-24, accessed 18 November 2026
- sys.dm_exec_background_job_queue (Transact-SQL) — Microsoft Learn, page dated 2026-09-21, accessed 18 November 2026
- Log Shipped database left in single user mode (Microsoft Q&A, answer by Microsoft External Staff) — Microsoft Learn Q&A, answer dated 2022-03-16, accessed 18 November 2026
This article was originally published on .