Cannot drop database because it is currently in use

dbtut February 10, 2019 MSSQL

If you want to delete the database, you will get this error if there is an open session on the database.

First, set the database to single_user mode. Then you can delete it.

USE [master]
GO
ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [Test]
GO

USE [master]

ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [Test]

With the above script, we close the open connections in the database and set the database as single_user so that only we can use it by ourselves and then delete it. If the database is already in single_user mode, you will get an error as follows.

Msg 5064, Level 16, State 1, Line 3

Changes to the state or options of database ” cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

Msg 5069, Level 16, State 1, Line 3

ALTER DATABASE statement failed.

Msg 3702, Level 16, State 4, Line 5

Cannot drop database “” because it is currently in use.

To manually kill all sessions that use the database in single_user mode, you must run the following query. You should write your own database name instead of “Test”.

select * from sys.sysprocesses where dbid=DB_ID('Test')

1	select * from sys.sysprocesses where dbid=DB_ID('Test')

The records returned as a result of the query are the processes on this database. You can find and kill the session_ids that use your database. Then you can delete the database. For example, if the result is 55, kill 55.

It will be safer to find and ask the person who sends the query before killing the query. If you think you don’t need to ask anyone, you can automatically kill all sessions in the database with the following script.

declare @Sql varchar(1000), @databasename varchar(100) 
set @databasename = 'You Must Write Your Database Name Here' 
set @Sql = ''  
select  @Sql = @Sql + 'kill ' + convert(char(10), spid) + ' ' 
from    master.dbo.sysprocesses 
where   db_name(dbid) = @databasename
     and 
     dbid <> 0 
     and 
     spid <> @@spid 
exec(@Sql)
GO

declare @Sql varchar(1000), @databasename varchar(100)

set @databasename = 'You Must Write Your Database Name Here'

set @Sql = ''

select @Sql = @Sql + 'kill ' + convert(char(10), spid) + ' '

from master.dbo.sysprocesses

where db_name(dbid) = @databasename

and

dbid <> 0

and

spid <> @@spid

exec(@Sql)

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

Cannot drop database because it is currently in use

About dbtut

One comment

Leave a Reply Cancel reply