The following script will kill all the sessions connected to database. It works well for 2012 and above , but i have not tried this on 2008 or 2005 version.I think it will work those versions too
*********************************************************************************************************************************************************************
declare @dbname1 varchar (60) ='<pass the database name here>'
declare @kilogin varchar(1000)
declare @killkilogin cursor
set @killkilogin=cursor
for select 'kill '+convert (varchar(50),spid)+';' from sys.sysprocesses
where dbid=DB_id(@dbname1)
open @killkilogin
fetch next from @killkilogin into @kilogin
while @@fetch_status=0
begin
declare @q1 varchar(1000)=@kilogin
exec (@kilogin)
print (@kilogin)
fetch next from @killkilogin into @kilogin
end
close @killkilogin
deallocate @killkilogin;
print 'all connection to '+@dbname1+' is killed '
go
declare @killkilogin cursor
set @killkilogin=cursor
for select 'kill '+convert (varchar(50),spid)+';' from sys.sysprocesses
where dbid=DB_id(@dbname1)
open @killkilogin
fetch next from @killkilogin into @kilogin
while @@fetch_status=0
begin
declare @q1 varchar(1000)=@kilogin
exec (@kilogin)
print (@kilogin)
fetch next from @killkilogin into @kilogin
end
close @killkilogin
deallocate @killkilogin;
print 'all connection to '+@dbname1+' is killed '
go
**********************************************************************************
Sometimes you will get the below error
Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.
That means you are running the above query in the database which you are wished to kill all
sessions connected to it. you the DIFFERENT DATABASE and rerun the above script.
**********************************************************************************
$servernames= gc F:\copypower2\server.txt
ReplyDeleteforeach($servername in $servernames){
$path="F:\copypower2\"
$str="connection made possible"
$str1=" connection not made to the server"
$computer=Test-connection -computer $servername -quiet
if($computer -eq "True")
{
remove-item \\$servername\F$\copypower2\put.txt
Add-Content F:\copypower2\conn.txt $servername$str" item removed was put.txt "
Copy-Item \\$servername\F$\copypower\put.txt -Destination \\$servername\F$\copypower2\
Add-Content F:\copypower2\conn.txt $servername$str" item added was put.txt "
}
else
{ Add-Content F:\copypower2\unconn.txt $servername$str1 }
}