Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Or try to ALTER the database at Single_USER –  user1592 Apr 27 '11 at 11:31
1  
Did you change to master before setting the database to single_user mode with rollback immediate ? –  bernd_k Apr 27 '11 at 11:34
    
As I pointed out, I have already done this. –  tuseau Apr 27 '11 at 12:25
    
well we once did the alter database single_user immediate from the database we wanted to drop. Very bad idea –  bernd_k Apr 27 '11 at 13:19
    
Yeah thanks anyway :) –  tuseau Apr 27 '11 at 14:56

7 Answers 7

up vote 8 down vote accepted

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

share|improve this answer
    
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 '11 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 '11 at 20:22
    
+1 I created an account here just so I could upvote this answer. It helped me a lot. Thanks! –  LeopardSkinPillBoxHat Apr 3 '12 at 3:57

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'
GO

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

USE [master]
GO

DROP DATABASE [yourdbname]
GO
share|improve this answer
    
... assuming, of course, it's OK to rollback uncommitted transactions –  swasheck Aug 6 '13 at 19:17

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
share|improve this answer

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

http://stackoverflow.com/a/7469167/261405

Here's the SQL from that answer:

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'YOUR_DABASE_NAME'

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
SELECT @SQL 
--Uncomment this to run it
--EXEC(@SQL)
share|improve this answer

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

share|improve this answer
2  
Welcome to the DBS.SE site! You may not have read the FAQ up until now, so let me inform you that posting link-only answers are highly discouraged. You can turn your answer into something really valuable by mentioning here the main points from that article. –  dezso Feb 14 '13 at 22:28

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?

share|improve this answer
    
2005. Thanks for the suggestions btw. –  tuseau Apr 27 '11 at 13:26

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.

share|improve this answer
    
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 '11 at 11:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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