use master
go
declare @dbname varchar(30), @spid varchar(10), @start datetime
select @start = current_timestamp, @dbname = 'pubs'
-- Timeout after 5 mts
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

This page was last updated on May 01, 2006 04:28 PM.