Ошибка sql database is in use

SQL Server keeps telling me a database is in use when I try to drop it or restore it, but when I run this metadata query:

select * from sys.sysprocesses 
where dbid 
  in (select database_id from sys.databases where name = 'NameOfDb')

It returns nothing.

Sometimes it will return 1 process which is a CHECKPOINT_QUEUE waittype. If I try to kill that process, it won’t let me (cannot kill a non-user process).

Anyone have any idea what’s wrong?

Oded

488k99 gold badges881 silver badges1007 bronze badges

asked Feb 24, 2011 at 13:01

i like this script. Do not struggle with killing..

use master

alter database xyz set single_user with rollback immediate

restore database xyz ...

alter database xyz set multi_user

answered Dec 2, 2012 at 18:10

MahmutHAKTANMahmutHAKTAN

6492 gold badges7 silver badges16 bronze badges

I was having the same issue when trying to restore a database from a backup. The solution for me was to ensure that I checked «Overrite the existing database(WITH REPLACE)» and «Close existing connections to destination database» in the Options page BEFORE doing the restore. Here is a screenshot below.

answered Aug 22, 2016 at 19:53

Judy007Judy007

5,4364 gold badges44 silver badges67 bronze badges

There could be lots of things blocking your database. For example, if you have a query window opened on that database, it would be locked by you. Not counting external accesses, like a web application on IIS.

If you really wanna force the drop on it, check the close existing connections option or try to manually stop SQL Server’s service.

answered Feb 24, 2011 at 13:40

SmurSmur

3,0657 gold badges28 silver badges46 bronze badges

Источник

Summary:
This blog will describe solutions to fix the ‘SQL database restore failed, database in use’ error. These solutions include disconnecting active connections (users and processes) to the database and by using Stellar Toolkit for MS SQL software to recover SQL database (db) from corrupt db or backup (.bak) file. The solutions apply to MS SQL Server 2019, 2017, 2016, 2014 and lower versions.

Contents

Reasons behind ‘SQL Database Restore Failed, Database in Use’ Error
Before We Begin
How to Fix ‘can’t restore SQL database because it is in use’ Problem?
What if the problem still persists?
Conclusion

When trying to restore SQL Server database from backup, it is common to receive an error message that reads as follows:

Restored failed for Server ‘xxx’ (Microsoft.SqlServer.SmoExtended)

Additional Information: System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use.

Figure 1: SQL Database Restore Failed Error Message

Reasons behind ‘SQL Database Restore Failed, Database in Use’ Error

Below are some reasons that could interfere with the restore process and throw the ‘restore of database failed because the database is in use’ error:

You are connected to the database you are trying to restore.
While using SQL Server Management Studio (SSMS) to do a database restore, you have more than one window open in it.
Other users are connected to the master db.

Now, we will discuss solutions to fix the error.

Tip: SQL Server database can be restored from the backup (.bak) file. But, the database restore operation may fail if the .bak file is corrupt. Use Stellar Toolkit for MS SQL software that comes with an efficient SQL backup extractor tool designed to help database administrators recover SQL database from corrupted backup (.BAK) file. The software supports SQL Server 2019, 2017, 2016, 2014, 2012, & older versions.

Before We Begin

Before proceeding with resolving the error – exclusive access could not be obtained because the database is in use, make sure to meet the following prerequisites:

SQL Server, of any version, must be installed on your system.
You will need SQL Server Management Studio (SSMS) installed on your computer.

How to Fix ‘can’t restore SQL database because it is in use’ Problem?

When attempting to restore SQL Server db, make sure there are no active connections. If someone is using the database, the restore operation will fail. To resolve the issue, you will need to disconnect the active users. You can do so, by following any of these methods:

NOTE: Before disconnecting the users, use SQL stored procedure ‘sp_who’ to check all users currently using the db. If you find users performing some important tasks, notify those users before disconnecting them. For detailed information on sp_who, refer to this link. If you don’t want to notify users, skip to method 2.

Method 1 – Close the existing connections to the database

To close existing connections to SQL db, follow these steps:

Step 1: Open SSMS and connect to the db.

Step 2: After connecting to the database, Object Explorer panel will appear on the left side of the SSMS window.

Step 3: In Object Explorer panel, right-click Databases, and then select Restore Database.

Figure 2: Restore Database

Step 4: In Restore Database dialog box, do the following:

Select one of the databases to restore.
In the left panel, click Options.

Step 5: In Options page, check the checkbox labeled, ‘close existing connections to destination database’.

Figure 3: Close Existing Connections

Once the SQL Server connections are closed, proceed with the restore operation.

Method 2 –Change from multiple-user mode to single-user mode

Changing the multiple-user mode by default to single user mode will disconnect all the connected users. This option can be used, if you want to disconnect all the users without notifying them.

To force users to go offline (i.e. disconnect) from SQL Server, set the db from multiple-user mode to single-user mode by following these steps:

Step 1: Open SSMS, connect to the database.

Step 2: In Object Explorer window, select New Query. Copy and paste the below T-SQL code snippet into the query window, and then click Execute:

USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

Object Explorer Window to execute new query

Figure 4: SSMS Query Editor

Executing the above code will change the database to single-user mode.

Method 3 – Restart the SQL Server Service

You can also disconnect the users by restarting the SQL service. You can restart the service by using SQL Server Configuration Manager, SSMS, services console, or the command line.

NOTE: Use this method as a last resort. That’s because, you may only need to restore a single db, but restarting the server will kill connections to all databases.

Steps to restart the service from SQL Server Configuration Manager are as follows:

Step 1: Browse SQL Server Configuration Manager using any of the following path:

SQL Server 2019 C:WindowsSysWOW64SQLServerManager15.msc

SQL Server 2017 C:WindowsSysWOW64SQLServerManager14.msc

SQL Server 2016 C:WindowsSysWOW64SQLServerManager13.msc

SQL Server 2014 C:WindowsSysWOW64SQLServerManager12.msc

SQL Server 2012 C:WindowsSysWOW64SQLServerManager11.msc

Step 2: In the left pane of SQL Server Configuration Manager window, click SQL Server Services. And in the right pane, right-click SQL Server service, and Stop and Start it.

Step 3: Click OK to exit the SQL Server Configuration Manager.

Figure 5: SQL Server Configuration Manager Window

What if the problem still persists?

If the issue still persists, likely there is a problem with your database or the backup file, used for restoring the database, is corrupt. In that case, use Stellar Toolkit for MS SQL. The software can recover db from a corrupt SQL Server. It can also extract a database – from corrupt backup (.bak) files – that need to be restored.

Stellar Toolkit for MS SQL software can also help you reset lost or forgotten SQL Server Administrator and user passwords. You can read the software review done by MVP from here.

To restore database from corrupt SQL Server backup (.bak) file by using the software, follow these steps:

Step 1: Download, install and launch Stellar Toolkit for MS SQL software.

Step 2: In software’s user interface, select Extract from MS SQL Backup.

Step 3: In Stellar Backup Extractor for MS SQL window, click Select File to choose the .bak file.

Figure 6: Select Backup (.bak) File

NOTE: Choose ‘Search in Folder’ option, if you do not know the file location.

Step 4: After selecting the .bak file, click Scan.

Step 5: The BackupSet window appears with details of all the backups.

Figure 7: List of Available Backups

Step 6: Choose the .bak file you want to recover from the Backup Type list, and then click Next to proceed with the scanning process.

Step 7: Once scanning is complete, a dialog box appears displaying the number of total records available in the backup file.

Step 8: The software shows a preview of the database records.

Step 9: To save the recovered .bak file, click Save on File menu.

Step 10: In the window that pops-up, choose MSSQL under Save As, and then click Browse to select the location to save the recovered file. Click OK.

Figure 8: Backup File Saving Formats

Step 11: Choose New Database or Live Database under Saving Options. Next, specify details required in Connect to Server section, and then click Connect.

Figure 9: Backup File Saving Options

Step 12: Click OK when the ‘Recovery process successfully completed’ message appears.

Figure 10 – Recovery Complete Message Box

The recovered file will get saved in the selected location.

You can watch the complete video from here:

Conclusion

This blog explained how to fix the SQL database restore failed, database in use problem. You can disconnect active users by closing the existing connections or by changing from multiple-user mode to single-user mode. Or, disconnect all the users by restarting the SQL Server service. But, if you still have issues restoring the db, Stellar SQL Database Toolkit can come in handy. It helps resolving the issue by repairing the corrupt SQL db or by recovering the SQL Server backup file.

About The Author

Priyanka Chauhan

Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software’s. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.

Источник

Error Details — SQL Database Is in Use

«I’m using SQL Server 2008 R2, I need to restore a database from a .bak file. There is always an error that the database is in use until I restart the SQL Server service.»

This is a comment on StackOverflow. As you see, when we try to restore a database in Microsoft SQL Server Management Studio (SSMS), it does not work because some other users are currently using the database, too. Usually, the full error message is like this.

Restored failed for Server
Additional Information: System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use.

Priyanka Chauhan

Источник

Error Details — SQL Database Is in Use

«I’m using SQL Server 2008 R2, I need to restore a database from a .bak file. There is always an error that the database is in use until I restart the SQL Server service.»

Restored failed for Server
Additional Information: System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use.

error - sql database is in use

Solutions — How to Fix Database in Use Problem

Applies to: MS SQL Server 2000, 20005, 2008, 2008 R2, 2012, 2014, 2016, 2017…

Any version of SQL Server, when this kind of problem occurs, can benefit from the provided solutions here. It’s possible to restart the Server service as the StackOverflow user does, but do not take it as the first step regarding the data safety. Next, we advise users to go through the four procedures to rectify the problem.

1. Check active users

Since the error message hints that someone is using the database, you need to check who it is and how to notify him/her to stop it. Usually, administrators use the built-in systems sp_who & sp_who2 to find processes and users using the database.

This is not for releasing the occupied database but it’s necessary to perform in advance! You don’t wish to disconnect a user who is doing a very important task without a notification.

2. Restart the service

It’s not recommended at the very first step, as we’ve explained, for data safety. On the other hand, if you didn’t find any active user by running sp_who or sp_who2 at the moment, it’s risk-free to restart the SQL server service.

There are five typical ways to restart the server service, including using the SSMS, Microsoft Services Console, Command Line, SQL Server Configuration Manager, and Powershell. Here, we’ll show you how to restart SQL Server service in Configuration Manager.

Step 1: Open SQL Server Configuration Manager. In Windows Server 2008 or 2003, for example, you can click Start > Programs > Microsoft SQL Server > Configuration Tools > SQL Server Configuration Manager.

Step 2: In the left pane of the SQL Server Configuration Manager, click SQL Server Services. All services are in the right pane. Right-click on SQL Server service and you can then select Restart from the context menu.

3. Start SQL Server in Single-user Mode

The other way to disable all the connected users without notifying them is to alter the default multiple-user mode to single-user mode. Use this code to abort all existing connections before attempting to restore.

USE master;
GO
ALTER DATABASE YourDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE YourDB
SET MULTI_USER;
GO

4. Fix Corrupted Database or Backups

If it’s true, trying to restart the service or disable connected users under single-user mode should be able to fix the error, and it won’t show up again during the SQL server restore process. Unless it’s the problem of database or backup itself.

Sometimes, for a similar situation that is hard to identify its cause after trying every possible means, it’s a good idea to follow the software’s way to recover, repair or rebuild the master database in SQL server.

EaseUS SQL Server Recovery software is exclusively designed for restoring a corrupted SQL database to a normal state. It’s economical and simple SQL database restore tool that works in most cases, such as SQL server shutdowns, corruption, backup failed, restore failed and even records were deleted. So, in the event that the former three solutions didn’t help you get rid of the failure, try EaseUS software this time. See if it works by repairing the corrupted database and backups.

Step 1. Close the SQL Service in Task Manager or SQL Server Manager Console.

Step 2. Run EaseUS MS SQL Recovery, click «Browse» (the two dots) or «Search» to choose the location where you saved your corrupted database. Afterward, Click «Repair» to start analyzing the specified database.

3. Start SQL Server in Single-user Mode

USE master;
GO
ALTER DATABASE YourDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE YourDB
SET MULTI_USER;
GO

4. Fix Corrupted Database or Backups

Step 1. Close the SQL Service in Task Manager or SQL Server Manager Console.

select the SQL database file

Step 3. When it has done, choose the files you want to repair and click «Export».

Step 4. Turn on the SQL service, you can choose to export the repaired files to database to export it as SQL scripts.

export the SQL database objects

Источник

A very neat feature of SQL Server is the way it lets the DBA to control connections. To this end, you can explicitly control connections with specific commands and methods. Even though this is not usually needed, there are some cases where you might need to make use of this feature. One such a case is when you are presented with the error:

“Exclusive access could not be obtained because the database is in use”

In SQL Server you might be presented with the above error message in the case you are trying to perform an operation on a database that requires exclusive access. One such operation is the RESTORE.

Exclusive access is when an operation needs to run on a database (or another SQL Server object) without any other operations/sessions accessing the same database/object at the same time.

How can you Handle this Error Message when Restoring the DB?

So, what can you do when you get this error message? You still need to perform the RESTORE right? 🙂

Here’s a common way of doing that:

1. Set the database in SINGLE_USER mode and forcibly terminate other connections:

USE [master]
ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

The above command will set the database in SINGLE_USER MODE and additionally the ROLLBABK IMMEDIATE termination option will roll back all the incomplete transactions. It will also disconnect any other connections to the database.

*Note: After issuing the above command, you will only be able to use a single connection to the database. So, if you are already using a query window connected to the target database, either restore the database by using the RESTORE T-SQL command or close the query window and restore the target database by using the Restore Database Wizard from SQL Server Management Studio.

2. Restore the database.

3. Set the database back to MULTI_USER mode:

USE [master]
ALTER DATABASE [DATABASE_NAME]
SET MULTI_USER;

That’s it! You have successfully restored the database!

An Alternative (More Complex) Way

Here’s an alternative way (a little bit more complex):

1. If there are any SQL Server logins granted any access on the target database then disable them:

alter login [LOGIN_NAME] disable;
GO

*Note: Even though now you have disabled the login(s) there still might exist active connections to the database.

2. To find and terminate existing connections on the target database perform the following:

-- Query that returns all the SPIDs (process IDs)/sessions established to a given database:
SELECT spid,loginame,login_time,program_name
FROM [master]..sysprocesses
WHERE DBID=DB_ID('DATABASE_NAME')

By collecting the above SPIDs you get the sessions that must be forcibly terminated (be careful not to forcibly terminate your own session! 🙂 By including the columns loginame, login_time and program_name in the above query, you are able to identify and exclude the spid that belongs to your session (that is, the DBA session).

You can terminate a session by executing the T-SQL Statement:

KILL @SPID

*Note 1: Replace @SPID with the appropriate SPID number.
*Note 2: You can even create a user-defined function undertaking this task.
*Note 3: You need to be very careful when terminating database processes. Never terminate a running process, since it may cause data integrity issues.

3. Set the database in SINGLE_USER mode:

USE [master]
ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER;

4. Restore the database.

5. Set the database back to MULTI_USER mode:

USE [master]
ALTER DATABASE [DATABASE_NAME]
SET MULTI_USER;

Remarks and Considerations

In the case of a RESTORE operation I would personally prefer the first method as it is simpler. With a single T-SQL Statement you set the database in SINGLE_USER MODE and also terminate all the active connections immediately and roll back all the incomplete transactions.

The second method is more preferable in cases where more “elegant” session control is required. This method allows the DBA to terminate sessions one-by-one explicitly instead of the first method which massively terminates all connections.

Choosing one of the two ways I guess that relies to the urgency of the RESTORE operation, the judgment of the DBA and also any Business Policies that might stand for such cases.

Because both the above-mentioned methodologies have to do with forcibly terminating connections to a database in SQL Server, you must be extremely careful when using them as you might accidentally cause severe data loss when applying them inappropriately and without the necessary authorization from the Management.

Enroll to our Master Class “Essential SQL Server Administration Tips”

If you really want to learn sophisticated SQL Server administration techniques, then you should check our on-demand online course titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

SQL Server Administration Tips (lifetime access – learn more).

Enroll from $12.99

Featured Online Courses:

Introduction to Azure SQL Database for Beginners
SQL Server 2019: What’s New – New and Enhanced Features
SQL Server Fundamentals – SQL Database for Beginners
Essential SQL Server Administration Tips
Boost SQL Server Database Performance with In-Memory OLTP
Essential SQL Server Development Tips for SQL Developers
Working with Python on Windows and SQL Server Databases
Introduction to Computer Programming for Beginners
.NET Programming for Beginners – Windows Forms with C#
Introduction to SQL Server Machine Learning Services
Entity Framework: Getting Started – Complete Beginners Guide
How to Import and Export Data in SQL Server Databases
Learn How to Install and Start Using SQL Server in 30 Mins
A Guide on How to Start and Monetize a Successful Blog

Ошибка sql database is in use

Reasons behind ‘SQL Database Restore Failed, Database in Use’ Error

Before We Begin

How to Fix ‘can’t restore SQL database because it is in use’ Problem?

Method 1 – Close the existing connections to the database

Method 2 –Change from multiple-user mode to single-user mode

Method 3 – Restart the SQL Server Service

What if the problem still persists?

Conclusion

About The Author

Error Details — SQL Database Is in Use

Error Details — SQL Database Is in Use

Solutions — How to Fix Database in Use Problem

1. Check active users

2. Restart the service

3. Start SQL Server in Single-user Mode

4. Fix Corrupted Database or Backups

3. Start SQL Server in Single-user Mode

4. Fix Corrupted Database or Backups

How can you Handle this Error Message when Restoring the DB?

1. Set the database in SINGLE_USER mode and forcibly terminate other connections:

2. Restore the database.

3. Set the database back to MULTI_USER mode:

An Alternative (More Complex) Way

1. If there are any SQL Server logins granted any access on the target database then disable them:

2. To find and terminate existing connections on the target database perform the following:

3. Set the database in SINGLE_USER mode:

4. Restore the database.

5. Set the database back to MULTI_USER mode:

Remarks and Considerations

Enroll to our Master Class “Essential SQL Server Administration Tips”

Featured Online Courses:

Related SQL Server Administration Articles:

Question

Answers

All replies