SP_Kill_Users
Posted by Philip Leitch
Saturday, June 13, 2009 5:22:49 AM
use master
go
create Proc SP_Kill_Users
@Database_Name as varchar(1000) = null
as
/*
www.prlsoftware.com
Author: Philip Leitch
Date: 2002
Purpose: Kills all processes currently using a specified database, or all databases if no database is supplied.
Copyright: Philip Leitch 2002
Licensing: This code may be used or modified but if the code is included in a software package attribution to me must be made.
Liability: The developer assumes all liability when using this code.
Notes:
Be very careful with this procedure Killing processes without first checking what they are is dangerous.
This procedure is very useful when a restore is required urgently.
Please note this procedure is intended to be placed into the master database, which combined with the SP_ prefix provides global calling scope.
*/
Declare [Kill_All] cursor FORWARD_ONLY
for
select databases.name, processes.spid from master.dbo.sysprocesses as processes
join master.dbo.sysdatabases as databases on
processes.dbid = databases.dbid
where hostname <> @@servername and hostname <>''
and (databases.name = @database_name or @database_name is null)
and processes.spid <> @@spid
group by databases.name, processes.spid
order by databases.name
open [Kill_All]
declare @KillSQL as varchar(1000)
declare @DatabaseName as varchar(100)
declare @SPID as varchar(10)
fetch [Kill_All] into @DatabaseName, @SPID
while @@fetch_status = 0
begin
set @KillSQL = 'Kill ' + cast(@spid as varchar(100))
exec (@KillSQL)
fetch [Kill_All] into @DatabaseName, @SPID
end
deallocate [Kill_All]
go
Copyright 2009 Philip Leitch