SQL Database Error 924 – Database is already open and can only have one user at a time

Written By Charanjeet Kaur
Approved By Kuljeet Singh
Updated on 10 Jul, 2024
Min Reading 3 Min
Share

Table of Content

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.

Free Download100% secure

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.

Was this article helpful?

YES0

About The Author

Charanjeet Kaur

Charanjeet is a Technical Content Writer at Stellar®who specializes in writing about databases, e-mail recovery, and e-mail migration solutions. She loves researching and developing content that helps database administrators, organizations and novices to fix multiple problems related to MS SQL and MySQL databases and Microsoft Exchange.

10 comments

Melanie R. Little says:

December 24, 2018 at 8:43 am

Recently, I have changed my database to single user mode and read-only. After making these changes, I get the SQL database Error 924 on the server.

Now, I can’t make this database for multiple users and kill any connections to this database.

Help!

Reply
1. Eric Simson says:
  
  December 24, 2018 at 5:52 am
  
  To overcome from this kind of situation, Try this:
  
  ALTER DATABASE [database name] SET MULTI_USER WITH ROLLBACK IMMEDIATE
  
  GO
  
  ALTER DATABASE [database name] SET READ_WRITE WITH NO_WAIT
  
  GO
  
  Reply
Cassandra H. Maxey says:

December 12, 2018 at 10:45 am

While executing the restore command in SQL Server Management Studio, I have received an error message:
“Exclusive access could not be obtained because the database is in use”
Then I got to Database Properties and switched it to Single Mode. After doing that, I have received one more error:
“SQL Database Error Message 924 database already open”
Now, I’m unable to switch it back to Multi User Mode.

Please provide solution to fix this error.

Reply
1. Eric Simson says:
  
  December 12, 2018 at 5:51 am
  
  Try the reference: Read More
  
  Reply
Joanne J. Bland says:

November 26, 2018 at 8:57 am

Our company can’t access SQL database due to SQL error 924. To fix this issue we have set SQL database in single user mode and used DBCC CHECKDB command (‘customers’,REPAIR_REBUILD). Through this method we have easily resolved this issue. The information provided in this blog is very useful and helped me to fix SQL error 924.

Reply
1. Eric Simson says:
  
  November 26, 2018 at 8:59 am
  
  Good to hear that you have access all data safely. Try to create backup on daily basis it will more helpful.
  
  Reply
Joseph says:

September 30, 2018 at 11:50 pm

When I double clicked on SQL icon it shows an error message ‘SQL Database Error 924: Database is already open and can only have one user at a time”. I don’t know why this problem occurs, except me, no one is using this software.

After using manual methods, I tried Stellar Repair for MS SQL tool to fix this problem. I really thankful to Stellar Team.

Reply
Samuel says:

July 31, 2018 at 4:46 am

I got this error “Msg 924 Database is already open and can only have one user at a time” when I try to switch to multi-user mode.

PLEASE HELP!

Reply
Stacy Roy says:

April 23, 2018 at 10:30 am

Thank you so much. I tried your software and it worked for me.

Reply
1. Eric Simson says:
  
  April 24, 2018 at 9:40 am
  
  Great!
  
  It is good to know that, our software fixed your problem.
  
  Reply