SQL: Drop / Kill Database Connections
If you need to drop database connections for a specific database, go read on..
This has been tested for Microsoft SQL Server 2005 and Microsoft SQL Server 2008.
This has been tested for Microsoft SQL Server 2005 and Microsoft SQL Server 2008.
Code starts here
use master
declare @vcdbname varchar(50)
/* @vcdbname => set it to the database you want the connections to be dropped.. */
Set @vcdbname = ‘cxnIM’
declare @vcdbname varchar(50)
/* @vcdbname => set it to the database you want the connections to be dropped.. */
Set @vcdbname = ‘cxnIM’
set nocount on
declare Users cursor for
select spid
from master..sysprocesses
where db_name(dbid) = @vcdbname
declare @spid int, @str varchar(255)
open users
fetch next from users into @spid
while @@fetch_status <> -1
begin
if @@fetch_status = 0
begin
print @spid
set @str = ‘kill ‘ + convert(varchar, @spid)
exec (@str)
end
fetch next from users into @spid
end
deallocate users
declare Users cursor for
select spid
from master..sysprocesses
where db_name(dbid) = @vcdbname
declare @spid int, @str varchar(255)
open users
fetch next from users into @spid
while @@fetch_status <> -1
begin
if @@fetch_status = 0
begin
print @spid
set @str = ‘kill ‘ + convert(varchar, @spid)
exec (@str)
end
fetch next from users into @spid
end
deallocate users
and ends here
Yorumlar