When I try to drop a database I get the error "Cannot drop database "dbname" because it is currently in use". However, when I run sp_who2, there are definitely no sessions connected to this database. I've also set the database to single_user mode with rollback immediate.

Why is this happening?

  • Since I can't "answer" this presently, see my answer to this other question. The error message you saw is a bit misleading. Zero spids may be "connected" to the database catalog, but at least one spid still holds a shared DB lock.
    – Granger
    Aug 27, 2020 at 22:14
  • SSMS has a simpler way to do this. From object explorer, right click on database > Delete. From the delete dialog, check close existing sessions. Unless you are dropping databases from a script, SSMS is the way to go. Aug 7, 2023 at 19:55

6 Answers 6


Make sure you don't have dependencies like database snapshots on the db you want to remove. Though, the error message would look otherwise. Are you sure that there is no hidden process that is connecting to your database? A good approach would be to run a script which kills all sessions and immediately after rename the database to another name and then drop database.

create a cursor based on this select:

  select  d.name , convert (smallint, req_spid) As spid
      from master.dbo.syslockinfo l, 
           master.dbo.spt_values v,
           master.dbo.spt_values x, 
           master.dbo.spt_values u, 
           master.dbo.sysdatabases d
      where   l.rsc_type = v.number 
      and v.type = 'LR' 
      and l.req_status = x.number 
      and x.type = 'LS' 
      and l.req_mode + 1 = u.number
      and u.type = 'L' 
      and l.rsc_dbid = d.dbid 
      and rsc_dbid = (select top 1 dbid from 
                      where name like 'my_db')

issue inside cursor:

SET @kill_process =  'KILL ' + @spid      
            EXEC master.dbo.sp_executesql @kill_process
                   PRINT 'killed spid : '+ @spid

after the cursor is closed and deallocated:

sp_dboption 'my_db', 'single user', 'TRUE'


sp_renamedb 'my_db', 'my_db_old'


  • Thanks for the code - that might work. What I don't understand is, what is a "hidden" session? I would have thought sp_who and the other metadata (DMVs) would show all sessions, otherwsie what use are they?
    – tuseau
    Apr 27, 2011 at 13:28
  • Yes normally you should be able to see all active/nonactive through sp_who or querying the sysprocesses table from master db. By hidden i meant a process that reconnects from an application service. Cheers.
    – yrushka
    Apr 27, 2011 at 20:22
  • 1
    This is antiquated for multiple reasons: (1) old-style joins (2) backward compatibility views (3) a cursor and dynamic SQL to run a bunch of KILL commands when a single ALTER will do (4) deprecated procedures like sp_dboption. Jul 20, 2015 at 13:24
  • 1
    Unfortunately I don't think this answers the question - the questioner is asking why this is happening, not how to solve. The answer provided works, but I still don't know what's keeping me from deleting a database. @AaronBertrand mentioned "even Object Explorer could be the culprit" which actually ended up being the reason for ONE of the databases, but how could I tell it was Object Explorer for sure? Jan 21, 2016 at 15:55
  • this gives me the error "Cannot use KILL to kill your own process"
    – nuander
    Jan 30, 2018 at 21:44

A session connected to another database might have an open transaction that also affects your database - sp_who2 will only show one database. It could also be something as simple as Object Explorer or Object Explorer Details open in SSMS, which again would only show one database in sp_who2.

Don't bother trying to find the session that is responsible; just kill them all with one statement (and make sure it isn't your copy of SSMS that is connected, e.g. another query window, Object Explorer, etc.):

USE master;

Now you will be able to drop it, and do that using DDL, not the UI:

  • 1
    Thanks for your answer, this worked. But I'm just having difficulty living with this solution: why is it that I cannot drop some databases due to this error? I have some databases that have not been touched for a year and there is no process or ostensible transaction that are connected to them. Could you give me some hints to help me find potential services, or transactions or anything that's connected to these databases? Jan 21, 2016 at 15:52
  • 2
    Actually, all I had to do was USE master, then DROP DATABASE dbname. Apparently all that's needed is to just "use" something else, to release the db.
    – vapcguy
    Aug 14, 2018 at 21:16
  • 3
    @vapcguy That's only true if your current query window is the only connection. This is not typically the case (and which is why my answers states "and make sure it isn't your copy of SSMS that is connected"). Aug 14, 2018 at 21:19

How about just seeing what SSMS does when you use the UI but tell it to issue a script for the action? Here is what SSMS does when you right click the DB and choose Delete, then check the box to close existing connections:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'yourdbname'

USE [master]

USE [master]

DROP DATABASE [yourdbname]

What's your current database when you issue the DROP command? Try this:

use master
drop database mydb

Also be sure that you are connected as sa and not dbo on whichever database you want to drop.

  • I'm definitely connected to master. I shouldn't have to be connected as sa to drop a database. This looks to me like a bug - it's not displaying a session, or it thinks there's a session in use but there isn't.
    – tuseau
    Apr 27, 2011 at 11:23
  • 3
    I just got caught out with this - tried to run the drop script with the context set to the database from the sqlcmd prompt! Doh
    – JonnyRaa
    Sep 23, 2014 at 9:21

I have faced this situation many times and below is what I do :

When obvious methods do not work .....(just like in your situation) :

Find out the database ID from sysdatabases.

Then execute - sp_lock that will show all the locks on the instance along with spid and dbid.

Kill the spids with the dbid that you are trying to offline or drop.

Though, the process is a bit manual, it can be automated as below :

IF OBJECT_ID('tempdb.dbo.#temp', 'U') IS NOT NULL
  DROP TABLE #temp;
create table #temp (spid int
                , dbid int
                ,ObjId bigint
                , IndId bigint
                ,Type varchar(5)
                ,resource varchar(max)
                ,Mode varchar(5)
                ,status varchar(10));
declare @dbid int
select @dbid =DB_ID(db_name())

insert into #temp
exec sp_lock

select * from #temp
where dbid = @dbid

Found really simple answer on StackOverflow that worked first time for me:


Here's the SQL from that answer:

DECLARE @DatabaseName nvarchar(50)

DECLARE @SQL varchar(max)

SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

--Use this to see results
--Uncomment this to run it

Not the answer you're looking for? Browse other questions tagged or ask your own question.