Sql server ошибка 924

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.

Want to Get Rid of SQL Server Error 924? Here is The Solution

This article is going to explore SQL Server error 924 along with its causes as well as solution. However, before we start, let’s quickly go through some user queries that can help us understand the issue that a user faces regarding the same. In addition to this,  this blog can help users understand the compete anatomy of this blog.

User Query 1: “SQL Server Error 924 is killing me! Whenever I try to access the database, I keep getting this error. I contacted the Database Administrator of the company but even after his multiple attempts, the problem could not be resolved. This problem is seriously bothering me and I am looking for a permanent solution to this issue. I hope that the experts will be able to help me out and provide me with a reliable solution to get rid of this error message.”

User Query 2: “I use the 2014 version of SQL Server. For the last few days, I get this message while accessing the database: ‘An exception occurred while executing a Transact-SQL statement or batch. SQL Error 924, Level 14, State 1, Line 1.’ I have no idea why this error is happening. I even tried to fix it using some SQL recovery applications but none of them worked. If there is any manual technique to stop this error message from occurring, kindly let me know. Thanks in advance.”

Overview of Error 924 in SQL Server

SQL Server users are familiar with its advantages as well as its disadvantages. So, there is nothing left to describe them about the errors of SQL Server. Users know that they will be encountering one or two error messages while using the SQL database. But the actual problem arises when the errors are stubborn and there is no sure-shot solution to fix it. One such error is SQL Database error 924. 

In this post, we will be discussing this error and its solutions. We will check out the manual techniques as well as an automatic software that can solve this error. But to begin with, we will discuss the reason for this problem.

SQL Server Error 924 – Know the Reason

Before going to the remedy part, everyone must be curious to know the cause of this error message. In most cases, nothing but the settings of SQL Server database are responsible for this recurring error message. The SQL database can have single user mode or multiple user mode. As the names suggest, single user mode will allow only one person at a time to access the database.

Now, if any second person tries to access the database while someone is already using it, that person is bound to get this error message. So, now we know what is the general cause of error 924 in SQL Server. However, if there is some corruption in the database, it might malfunction and show this error despite the fact that no one else is using the database.

Fix Microsoft SQL Database Error 924 Manually

Now that you know the causes of SQL server error 924, it is time to resolve it. Here we have listed some of the popular manual techniques adopted by the users in such situations:

1. Restart SQL Service: If this problem is happening, you can restart the service of SQL Server. Upon restart, the error message might just vanish.
2. Restore Backup: Restoring last good backup might also be a good idea for this error. But you need to make sure that the backup is recent and does not have any corruption.
3. Verify SQL Setting: Check the status of the SQL setting.

• First run this below command. If the single mode is set, the status column will come up with ‘single user’ status.

sp_helpdb
go

• Run sp_who to know the person who is using the database currently. You will be able to track that person from login name column. You can contact that person and fix a time for database access.
•  You can also ask the system admin or database owner to change the setting into multi-user. The change has to be done when the database is not in use.

sp_dboption , single, false
go
use
go
checkpoint
go

Troubleshooting Microsoft SQL Server Error 924 

We have given several manual methods in the earlier section of this article. Users can try any of those. However, none of them guarantees the confirm removal of this error. In such cases, users are free to try SQL Database Recovery Software to resolve SQL database error 924. This application has won the trust of thousands of SQL database administrators and users. The cited tool is often used to remove corruptions of SQL database.

Download Tool Purchase Now

• Support All Database Files: Whether the problem lies in MDF, NDF, or LDF file, the software can remove it without any miss.
• Dual Scanning Mode: Depending on the severity of database corruption, users can choose between Quick and Advanced scanning mode.
• Save Scanning Result: The program allows to save the result of database scanning. It saves the time for scanning it again if it becomes necessary.
• Advanced Data Type Compatibility: Along with recovering all components of SQL Server database, the tool provides support for advanced data types like ASCII and Unicode XML.

Final Thoughts

SQL database users know that error SQL Server error 924 can be a stubborn one. Therefore, we have suggested multiple methods that can be applied to fix this problem. Unfortunately, the manual methods are not completely equipped to stop this error message from occurring.

Therefore, the world has seen the invention of third-party tools. SQL Recovery Software is considered as the trusted solution to resolve SQL error 924 by SQL DBAs and MVPs across the globe. Due to its features mentioned above, we recommend this application for any type of Microsoft SQL Server error including Microsoft SQL database error 924.

FAQs

Q-1. How do I fix SQL connection error?

Ans: There are a few ways for users to check the connection error like verifying the following aspects:

• Running Instance.
• Firewall Configuration.
• Aliases on Client’s Machine.
• Running SQL Server Browser.
• Server Name in Connection String.

Q-2. Which method is better to fix error 924 in SQL Server?

Ans: Well, both manual as well as automated solution works well. However, we must recommend the modern tool for a smooth hassle-free experience.

Q-3. What all database objects this software is capable to repair & recover?

Ans: This software can easily fix error 924 SQL Server & the objects like table, triggers, stored procedure, views, functions, etc. 

SQL Server Error 924 : Database Already In Use and Can Only be Used by One User

Hello all! I am using SQL Server 2012 in my firm. Few days back, I had changed the settings of one of the server databases to read-only mode. But, don’t know why, I got stuck while working with this database and encountered an error message i.e., Microsoft SQL Server 2012 error 924. As per my knowledge, I tried several PowerShell commands and also tried to disable read-only mode but, nothing worked. Can anyone recommend that what can be done to get rid of this because the respective server is used for production purpose and tough to restart in midweek?

Alike the above, there are my Microsoft server clients who shared this problem with Invorx team of SQL experts. Therefore, here we came with this informative post to cover description of troubleshooting 924 error in SQL Server, which is generally associated with database corruption.

The error occurs somewhat in a following manner :

What to Do to Get Out of This MS SQL Server Error 924?

All possible solutions through which one can get out of this error message are explained in brief, in this today’s post. But before that, we want to tell readers that the illustrated solutions don’t support older editions of the server. They are workable on SQL Server 2008 and above versions. Also, it employs on any of the server versions that includes Express Edition.

Important Points to Remember :

1. The Microsoft SQL server 2012 error 924 has the level 14.
2. It belongs to the security level errors like – permission denied. It states that it can’t be opened because another user is accessing it.

First of all, it is essential to determine the error cause before troubleshooting it. So, analyze your server processes to address the user who is accessing your required resource.

1. Through the Stored Procedures – Make use of sp_who or sp_who2 stored procedures. One can also utilize kill procedures to terminate all processes that are active in database.
2. With the Help of MS SQL Profiler – This option is to address the server activity via SQL profiler, but to acquire consideration, which the profiler would be deprecated in coming editions.

Lost Your Patience But, Error Not Fixed?

If the above-mentioned solutions don’t fix your issues, you can move further with settings of your database in ‘single user’ mode. This can be done with help of SSMS and its properties.

You can also do the same to fix Microsoft SQL Server 2012 error 924 with help of T-SQL command. Execute following statements for this :

#1 : Restore Back The Complete Database

Explore the data of backup file to restore its content. Ensure that you have updated the backup file. If you don’t have latest database and replica of recent database then, proceed with next hope.

#2 : Recheck All Services of The SQL Server

This demands for restarting of the SQL server services. It might fix the problem that can be resolved using SQL Server configuration manager.

#3 : Make Use of DBCC CHECK Repair Options

If nothing works, try to troubleshoot the database with “DBCC CHECKDB (‘xyz’, REPAIR_REBUILD)” command. Still if the error continues, execute DBCC CHECKDB (‘xyz’, REPAIR_ALLOW_DATA_LOSS), where xyz is the database name.

Its Time to End With Some Suggestion Statements

All manual methods through which one can fix Microsoft SQL server error 924 are mentioned in this post. But, if in case corruption in the SQL server database is present and it is not getting troubleshooted with those approaches, the only solution left is use of third-party SQL recovery software. Enterprises need to purchase licensed version of anyone of the chosen products and use it to resolve the SQL Server 2012 error 924.

How to Solve SQL Error 924?

Microsoft SQL users are familiar with its pros and cons. So, there’s nothing left to explain to them the errors of SQL Server. Users recognize that they will be encountering one or two error messages while using the SQL database. But the actual problem arises when the errors are stubborn and there is no sure-shot solution to fix it.

This article offers you an overview of what you can do to troubleshoot SQL Error 924. This problem is usually related to database corruption.

The error indicates the following message:

What to do to solve SQL Server Error 924?

All possible solutions through which one can solve this error message are explained in brief here in this post. But before that, we wish to inform readers that the illustrated solutions don’t support older editions of the server. They are workable on SQL Server 2008 and above versions. Also, it employs any of the server versions that include Express Edition.

Important Points to Remember :

1. The Microsoft SQL server 2012 error 924 has level 14.

2. It belongs to the security level errors like – permission denied. It states that it can’t be opened because another user is accessing it.

Initially, it’s essential to find out the cause of error before troubleshooting it. So, analyze your server processes to know about the user who is accessing your required resource.

1. Through the Stored Procedures – Make use of sp_who or sp_who2 stored procedures. One can also utilize kill procedures to terminate all processes that are active in the database.

2. With the Help of SQL Profiler – This option is to address the server activity via SQL profiler but to acquire consideration, which the profiler would be deprecated in coming editions.

Manual Techniques To Solve SQL Error 924

If the above-named solutions don’t fix your issues, you can move further with settings of your database in ‘single user’ mode. This can be done with the help of SSMS and its properties.

You can also do the same to fix MS SQL Server 2012 error 924 with the help of the T-SQL command. Execute the following statements for this :

1. Restore the Database from Backup

A user can easily restore the database by using a backup. To practice this method, it is essential to have an updated backup. If you don’t have any backup for your database then try another method.

2. Check SQL Server Services

There could be settings failure in the Server Services that might result in this issue. Therefore, it is advised to restart the SQL Server service, which may solve the problem. If the problem still exists, try to use the SQL Server Configuration Manager.

3. DBCC CHECKDB Repair Options

A user can easily repair the database using the following options, where “abc” is the database name,

DBCC CHECKDB(‘abc‘,REPAIR_REBUILD)

If it does not work, try using this,

DBCC CHECKDB(‘abc‘,REPAIR_ALLOW_DATA_LOSS)

4. Verify the Database Accessed is in Single-User Mode

Firstly, check the status of the database by executing the command below:
sp_helpdb  

go
Note: If the database is in a single mode, it is shown as a “single-user” in the status column.

Now, execute sp_who to find out who is accessing the database. Now, check the database name under dbname column and the login name from the loginame column.

Either contact the system administrator “sa” or the individual to rearrange the database access. The “sa” or database owner “dbo” can set the database to provide multi-user access. Make sure the file is not in use which is followed as:

sp_dboption , single, false 

go

 use  

go

checkpoint

go

Final Words

In this post we have discussed, all manual methods through which one can fix Microsoft SQL error 924. But, if in case corruption in the SQL server database is present and it is not getting troubleshooting with those approaches, the only solution left is the use of third-party SQL recovery software. This can be resolved using third party utility which enables us to repair SQL errors and even support the latest SQL server 2016 version. 

You can also read:

Best Methods to Resolve “Cannot Open Default database Error 4064” in SQL Server

Related Post