Dynamic SQL Kill statements

Hi all

Like me, I am sure you get miffed when you do testing, and you need to restore a db to a specific backup point, only to get the message " database "Cannot restore database "xxx" because it is currently in use". Then it’s back to running the tedious "sp_who, kill " queries (or using the management tool).

Below is a script I wrote that you can use to kill all the connections to a list of DB's.

You'll notice the variable in one of the first lines, named " @List_Of_Databases". To use the script, simply type the name of the DB's you want to disconnect, separating with a comma. Then run it... It will kill all processes running against those DB's listed. Then you can drop/restore the db to your heart's content.

One word of warning - I have seen that, if SSIS is in "execution" mode (whether it is completed or not), and you run the script, it dies a horrible, painful death.

Enjoy...
/* START OF SCRIPT */


use master
go
Set NoCount on
go
declare @List_Of_Databases varchar(200)
-- List of databases to disconnect
/**********************************************************
User types the list of DB's here
***********************************************************/
select @List_Of_Databases =
'DB1, DB2, DB3, DB4'




--Parse the list to eliminate case problems
Select @List_Of_Databases = UPPER(RTRIM(LTRIM(@List_Of_Databases)))
-- Table to store DB's
Create table #DatabaseList
(
DBNAME varchar(200)
)
--Build the query to get the list of DB's
select @List_Of_Databases = 'select name from sysDatabases where UPPER(RTRIM(LTRIM(Name))) in (' + char(39) + replace(replace(replace(@List_Of_Databases,' ,',','),', ',','),',',char(39) + ',' + char(39)) + char(39) + ')'
--Get the DB's that match the names
Insert into #DatabaseList
exec (@List_Of_Databases)
-- Temp table to store SPID's in use
Create table #Table
(
RowID int identity(1,1)
,SPID int
,UserName varchar(200)
,DBName varchar(200)
,LoginTime datetime
,LastRun datetime
,Program varchar(200)
)
--Get the SPID's
Insert into #Table
(
SPID
,UserName
,DBName
,LoginTime
,LastRun
,Program
)
select spID
,LogiName
,SD.Name
,Login_time
,Last_Batch
,Program_Name
from master.dbo.sysProcesses SP
join master.dbo.sysDatabases SD
on SP.dbID = SD.dbID
join #DatabaseList DB_List
on SD.Name = DB_List.DBNAME
Where spID <> @@SPID
declare @RowID int
,@MaxRows int
,@SQL varchar(200)
,@SPID int
,@UserName varchar(200)
,@DBName varchar(200)
,@LoginTime datetime
,@LastRun datetime
,@Program varchar(200)
-- Set for Loop
Select @RowID = isnull(min (RowID),1)
,@MaxRows = isnull(max (RowID),0)
from #Table
--Perform Loop
While @RowID <= @MaxRows
begin
-- Get process details
Select @SPID = SPID
,@UserName = UserName
,@DBName = DBName
,@LoginTime = LogInTime
,@LastRun = LastRun
,@Program = Program
from #Table
where RowID = @RowID
-- Build query to kill process
Select @SQL = 'kill ' + convert(varchar(20),@SPID)
-- Kill process & show message
exec (@SQL)
Print 'Killed process details' + char(13)
+ '======================' + char(13)
+ 'Process ID: ' + convert(varchar(20),@SPID) + char(13)
+ 'UserName: ' + @UserName + char(13)
+ 'DataBase: ' + @DBName + char(13)
+ 'User Login time: ' + convert(varchar(50),@LoginTime,100) + char(13)
+ 'Last Query run by user: ' + convert(varchar(50),@LastRun,100) + char(13)
+ 'User Interface: ' + @Program + char(13) + char(13)
-- Iterate loop
select @RowID = min(RowID)
from #Table
Where RowID > @RowID
end
if @RowID is not null -- No rows were processed
begin
print 'No processes were killed'
end
-- Clean up
Drop table #Table
drop table #DatabaseList
dbcc FREEPROCCACHE WITH NO_INFOMSGS

/* END OF SCRIPT */