Summary: This blog provides an overview of what you can do to troubleshoot SQL Database Error 924. You can fix this error using T-SQL queries, SSMS, or SQL recovery software.
SQL error 924, “Database ‘%.*ls’ is already open and can only have one user at a time”, is an error with level 14. Level 14 belongs to security level errors like a permission denied error. It means that you cannot open a database because someone is using it. The 924 error usually happens if you attempt to access a SQL database set to SINGLE_USER mode.
Here’s how the complete error message reads as:
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.
What Should We Do?
Since the 924 error 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, launch SQL Server Management Studio (SSMS) and connect to a server instance. Right-click on the database, click on Options, then navigate to the State section to check your database state. If it is set to SINGLE_USER mode, you must find and kill any processes using the database. You can do so by using any of these options:
- Using System-Stored Procedures
Use the officially documented sp_who or undocumented sp_who2 stored procedure to find any currently running processes or sessions.
| EXEC sp_who |
This command returns session id (spid) of active connection in an instance of SQL Server.
| EXEC sp_who2 |
This 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 SQL Profiler, but consider that SQL Profiler will be deprecated in future versions.
If any other process uses the database, use the KILL command to kill that process.
Still Can’t Access the Database?
If you are still getting the error and are unable to access the database and data, try the following solutions.
Solution 1: Check SQL Server Services
Sometimes, simply restarting your SQL Server service may solve the problem. If this doesn’t work, try the next solution.
Solution 2: Restore the Database from Backup
Restore the database using a backup. But, ensure that you have an updated working backup. If the backup doesn’t work or is obsolete, repair the database.
Solution 3: DBCC CHECKDB Repair Options
To regain access to the data, try repairing the database using any of these DBCC CHECKDB repair options:
DBCC CHECKDB('xyz',REPAIR_REBUILD)If it does not work, try the following:
DBCC CHECKDB('xyz',REPAIR_ALLOW_DATA_LOSS) Replace ‘xyz’ with the name of the database you want to repair.
Using the ‘REPAIR_ALLOW_DATA_LOSS’ option, as the name suggests, can lead to data loss. Instead, consider using a SQL recovery tool to repair the database while preserving data integrity.
Solution 4: Use SQL Database Recovery Software
Stellar Repair for MS SQL is an advanced SQL recovery tool that repairs the database MDF file and restores the data to a new or an existing SQL database. The software also helps recover all the components from the repaired file, including tables, deleted records, keys, indexes, triggers, stored procedures, etc.
Understand how the software works by checking out this video:
Conclusion
SQL Database Error 924 appears if you or any other user tries accessing a database in SINGLE_USER mode. Since only a single user can access the database, you receive the “Database is already open and can only have one user at a time” error.
To make the database accessible again, you must check and kill any process, session, or login id that holds the database, as discussed in this blog. 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. Or else, you will need to repair the database using DBCC CHECKDB repair options or Stellar Repair for MS SQL software.
