use master
go
declare @dbname varchar(30), @spid varchar(10), @start datetime
select @start = current_timestamp, @dbname = 'pubs'
while(exists(Select * from sysprocesses where dbid = db_id(@dbname)) and
datediff(mi, @start, current_timestamp) < 5)
begin
declare spids cursor for
select convert(varchar, spid) from sysprocesses
where dbid = db_id(@dbname)
open spids
while(1=1)
begin
fetch spids into @spid
if @@fetch_status < 0 break
exec('kill ' + @spid)
end
deallocate spids
end
if not exists(Select * from sysprocesses where dbid = db_id(@dbname))
exec sp_dboption @dbname, offline, true
else
begin
print 'The following processes are still using the database:'
select spid, cmd, status, last_batch, open_tran, program_name, hostname
from sysprocesses
where dbid = db_id(@dbname)
end
go |