Resolve Error “SQL Database is in Use” Using Manual Steps

Problem while Working On Database:

While working on SQL Server users perform various actions such as Rename, Detach, Restore, and Drop on SQL Server. Due to which an error occurs “SQL Database is in use” with different error numbers while performing such actions on SQL database.

How to Tackle this Problem ?

Some scenarios are discussed below, which helps users to resolve the occurrence of an error while executing various actions on SQL database.

Scenario 1: While Restoring the Database

When the user tries to restore the database and other users are accessing the same database at that time. Then, they face SQL error message 3101, which is due to SQL database is in use. There are some solutions, which helps users to remove the error occurrence.

Exclusive Access

User can get the exclusive access by dropping all the other connections to restore the database. There is another way to check the connections usage of the database, which user needs to restore, i.e. sp_who2 and SSMS.

Using KILL Command

After getting exclusive access, user can use KILL command that helps to kill every connection that is using the database. Before this, user must be aware that which connections are killed as the rollback issue may be required.

ALTER DATABASE

There is another option, in which user can put the database in single user mode after that they can restore. It also rollback depends on the option user use, but it will all do connections at once.

Scenario 2: Renaming the Database

While renaming the database user receives SQL error 5030. This error occurs when the database is in Multi User mode where various users are utilizing the database at same time. To resolve this issue, user first need to set the database to single user mode and then rename the database and after that set it to Multi user mode again. User can follow the steps given below to perform the process.

• Firstly set the database to single user mode,
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE
• Now rename the database
ALTER database AdventureWorks MODIFY NAME = NewAdventureWorks
• Set database to Multiuser mode
ALTER DATABASE AdventureWorks SET MULTI_USER WITH ROLLBACK IMMEDIATE

Scenario 3: While Dropping the Database

When the user tries to drop a database when users are connected to SQL Server database, then they will receive SQL error message 3702. User can follow the mentioned steps to remove the error occurrence by getting the exclusive excess to drop database in Server.

1. Connect to SSMS and expand the database node.
2. Right click on the databases that are required to drop.
3. Choose the delete option from drop down list to view the delete object.
4. Now choose the checkbox “close existing connections” to drop existing connections before dropping the SQL Server database.
5. Select option “Delete backup and restore history information for database,” user will be able to remove the database backup and restore history that is stored in database.

Using TSQL Query

User can execute the TSQL code to drop the database in SQL Server by using TSQL Query as mentioned below:

EXEC msdb.dbo.sp_delete_database_bakuphistory @database_name = N‘AdventureWorks’
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Drop DATABASE [AdventureWorks]
GO

Scenario 4: Detaching the Database

When the users detach the database, they face SQL Server error 3703. This error is due to the reason that multi users are using the same database at the time. To resolve this, user can kill the respective accessing DB processes; sometimes it does not work. For this, user needs to force DB delete/detach by ending the existing connections by leaving the DB in single user mode.

Select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
where d name like ‘%mydb%’
go
kill 53
go

Users need to fix the DB offline with ‘SET OFFLINE WITH ROLLBACK IMMEDIATE’ setting so that the DB will be turn offline immediately and after that detach DB will work correctly. When the user needs to attach the DB they can make the DB online.

ALTER DATABASE  SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [Database Name] SET ONLINE

Conclusion

With the help of the discussed solution, user can easily resolve an error “SQL Database is in Use” to perform various actions such as restore, rename, detach, and drop on the SQL server database.