SQL Server Cannot drop database <dbname> because it is currently in use… but no sessions displayed

Question

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?

Answer 1

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 master..sysdatabases 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'

go

sp_renamedb 'my_db', 'my_db_old'

go

DROP DATABASE MY_DB_OLD

Answer 2

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

use master
go
drop database mydb
go

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

Answer 3

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. What version of SQL Server is this?

Answer 4

There is a nice article where the same issue is discussed with few options. http://gilgh.com/article/unable_to_drop_database,_Currently_in_Use