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


  

 --The query to find the spid for the db in use
 declare @spid smallint
 select  @spid= convert (smallint, req_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 = 'mydb')

declare @kill_process nvarchar(2000) 
SET @kill_process =  N'KILL ' +Cast(@spid as nvarchar(10)) 

--kill the db in use process
EXEC master.dbo.sp_executesql @kill_process
 PRINT 'killed spid : '+ Cast(@spid as nvarchar(10)) 

Go

if db_id('mydb') is not null
alter database mydb 
set single_user with rollback immediate;
go
--rename database to another name
sp_renamedb 'mydb', 'mydb_old'

go
--drop the db with the new name
DROP DATABASE mydb_old 

  
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s