This article is to provide an overview of what you can do to troubleshoot SQL Database Error 924. This problem is usually related to a database corruption.
The error indicates the following 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.
What should we do?
In this article, we will show different possible solutions for this error, including native T-SQL solutions, UI solutions and SQL database repair software.
This article applies to SQL Server 2008 until SQL Server 2017. Older versions are no longer supported. It also applies to any SQL Server edition including the Express Edition.
- The error 924, Database ‘%.*ls’ is already open and can only have one user at a time, is an error with level 14.
- The level 14 belongs to security level errors like a permission denied. It means that it cannot be open because someone is using it.
To verify the reason for this error, check your processes to verify which one can be using your process.
- Using Stored Stored Procedures
Use the sp_who or sp_who2 stored procedures. You can also use the kill command to kill the processes that are active in the database.
Note: sp_who command is documented and officially supported by Microsoft. sp_who2 command is undocumented and doesn’t have any support from Microsoft.
- Using SQL Profiler
Another option is to check the activity using SQL Profiler, but take in consideration that SQL Profiler will be deprecated in future versions.
Fix SQL Server Error 924
If that does not solve your problem, you can set your database to single user mode. You can do that using the SSMS and the properties.
You can also use T-SQL in SSMS, sqlcmd or any tool of your preference to write T-SQL commands:
ALTER DATABASE youdbname
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Solution 1: Restore the Database from Backup
Restore the database using a backup. Make sure to have an updated backup. If you do not have a current database and all the previous options did not work, there is another hope.
Solution 2: Check SQL Server Services
If it does not work either, restart your SQL Server service. It may solve the problem. Try to use the SQL Server Configuration Manager.
Solution 3: DBCC CHECKDB Repair Options
After that you can try to repair your database with the following options:
If it does not work, try the following:
Where ‘xyz’ is database name.
Solution 4: Use SQL database repair software
Stellar Repair for MS SQL is a nice tool used to restore the corrupted database. You need to know the location of your MDF file. This MDF file contains all your data information. It supports MS SQL 2019, 2017, 2016 and lower versions. You need to download the tool here:
This tool can help you to repair a data file. You may need to stop your SQL Server database and then select the MDF file to repair.
You just need to press the Select Database option and select your MS SQL Database file. Or press the Search button to search in a specified folder.
Search in folder allows specifying where to search. You can search also in subfolders. Once you have your data file selected, press the Repair button and that is all. You will have your database repaired.
This software can be downloaded easily, and available in three versions:
Technician version repairs the corrupt SQL database. The SQL Platinum Edition includes software to repair the SQL Server backup also. Finally, the SQL Database Toolkit includes not only the SQL Database Repair and the Backup repair modules, but also a module to recover SQL Server administrator and user passwords. Read more
It is also possible to scan the results and repair the database later. You can also save your result reports in MS SQL (MDF), XLS, HTML and a CSV file.
The software can repair page errors, database object like a table, view, stored procedure, function, etc. You can check the complete information on the product page.
The minimum memory requirement is 1 GB. It can be installed on Windows 10 and below versions. The software requires 50 MB of free space and the current version is 8. The software creates a repaired data file that is ready to use with either new or live database.
Tables with row and page compression can be easily recovered with this tool. If you need to migrate your license, the software allows migrating to a different server if necessary.
In this article, we learned how to handle and repair the database if the SQL Database Error 924 appears. We can start setting the database in single user mode and then trying to repair the database using DBCC.
Finally, we show how to use the Stellar Repair for MS SQL to repair damaged SQL database file. It is only necessary to stop SQL Server, find the MDF file-related and press repair.