How to Fix SQL Database Error – 924?

You can encounter the error “Database ‘db_name’ is already open and can only have one user at a time (Microsoft SQL Server, Error 924)” while creating or restoring the backup. It is an error with level 14, which indicates it belongs to the security level errors, like permission denied error. The 924 error usually occurs if you attempt to access the SQL database that is set to SINGLE_USER mode.

Here’s the complete error message:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Msg 924, Level 14, State 1, Line 1
Database 'db_name’ is already open and can only have one user at a time.Copy Code

Methods to Resolve “Database is already open and can only have one user at a time” (Error 924) in MS SQL

Since the error 924 is associated with a single-user connection problem, you must first check if the database you’re trying to access is in SINGLE_USER mode or not. For this, follow the below steps:

• Launch SQL Server Management Studio (SSMS) and connect to a server instance.
• Right-click on the database and select Properties.
• In the Properties window, click on Options, and then navigate to the State section. Next, check your database state.

• If it is set to SINGLE_USER mode, you must find and kill any processes using the database.

To do so, you can use any of these options:

• Using System-Stored Procedures

You can use the officially documented sp who or undocumented sp who2 stored procedure to find any currently running processes or sessions and who is accessing the database.

The EXEC sp_who command returns the session id (spid) of active connection in the instance of SQL Server. 

The EXEC sp_who2 command shows more details than sp_who, including all the running system and user processes.

• Using SQL Profiler

Another option is to check the activity using the SQL Profiler. If any other process is using the database, you can use the KILL command to kill that process. Here’s how to execute the command:

KILL 2
Where 2= spid number.Copy Code

After that, set the database to MULTI_USER mode using the below command:

ALTER DATABASE Dbtesting SET MULTI_USERCopy Code

Still can’t Access the Database?

If you are still getting the error and are unable to access the database, try the following solutions.

Solution 1: Restart SQL Server Services

Sometimes, simply restarting the SQL Server services may resolve the problem. If this doesn’t work, try the next solution.

Solution 2: Restore the Database from Backup

You can restore the database from backup. This will reset the database to a previous state when the database was not in SINGLE_USER mode. First, ensure that you have an updated working backup. You can use the RESTORE VERIFYONLY  command to verify the backup. If the backup is readable, then use the below command to restore the .bak file:

USE [master];
BACKUP DATABASE [testing]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\testing.bak'
WITH NOFORMAT, NOINIT,
NAME = N'testing-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GOCopy Code

Solution 3: DBCC CHECKDB Repair Options

If the backup is obsolete or not working, you can repair the database using the DBCC CHECKDB command. You can try repairing the database using any of these DBCC CHECKDB repair options:

DBCC CHECKDB ('xyz',REPAIR_REBUILD)Copy Code

If it does not work, try the following:

 DBCC CHECKDB ('xyz',REPAIR_ALLOW_DATA_LOSS)Copy Code

Here, replace ‘xyz’ with the name of the database you want to repair.

Note: Using the ‘REPAIR_ALLOW_DATA_LOSS’ option, as the name suggests, can lead to data loss.

Solution 4: Use an Advanced SQL Database Recovery Software

Stellar Repair for MS SQL is an advanced SQL database recovery software that quickly repairs the database file and restores the data to a new or an existing SQL database. The software can recover all the components from the repaired file, including tables, keys, indexes, triggers, stored procedures, and even deleted records, with complete integrity. It also allows you to save the repaired database data in multiple file formats. It helps in resolving a wide range of corruption-related errors in SQL Server. The software supports repairing of databases on both Windows and Linux systems.

To understand how the software works, watch this video:

Conclusion

The SQL database error 924 appears if multiple users try to access the database, which is in SINGLE_USER mode. To make the database accessible, you must check and kill any active process, session, or login ID that holds the database. If the database remains inaccessible, restart the server to check if it fixes the issue. If not, you may need to restore the database from a healthy and updated backup. Else, you need to repair the database using the DBCC CHECKDB commands or Stellar Repair for MS SQL software.

Was this article helpful?

YES0

NO

About The Author

Monika Dadool

Monika Dadool is a Senior Content Writer at Stellar with over 5 years of experience in technical writing. She is a tech enthusiast and expert who specializes in writing about SQL Server, MySQL Server, MariaDB Server, Microsoft Access, Active Directory, email recovery, Microsoft 365, pattern recognition, machine learning, data recovery, file repair, and operating systems like Linux, Windows, and Mac. She also writes about accounting software such as QuickBooks and Sage 50, as well as web-scripting languages like HTML, JavaScript, Python, PHP, Visual Basic, ASP.NET, and AJAX. Monika is passionate about researching and exploring new technologies, and she enjoys developing engaging technical blogs that help organizations and database administrators resolve various issues. When she's not creating content, you can find her on social media, watching web series, reading books, or exploring new food recipes.