Nov 12 2009 November 12, 2009 November 12, 2009 1Comment by christian

SQL: Drop / Kill Database Connections

Posted in ,
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.		 Logo_Ms_SQL

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’

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

and ends here