You can run this, replace the DBID with the correct ID from sysdatabases
/*************************************/
DECLARE SpidsToKill CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE dbid = 9
DECLARE @Spid int
DECLARE @SQL varchar(255)
OPEN SpidsToKill
FETCH NEXT FROM SpidsToKill
INTO @Spid
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SELECT @SQL = 'KILL ' + CONVERT( varchar(10), @Spid )
EXEC ( @SQL )
FETCH NEXT FROM SpidsToKill
INTO @Spid
END
CLOSE SpidsToKill
DEALLOCATE SpidsToKill
*/////////////////////////////////*
-----Original Message-----
From: Sanchez Anthony F
[mailto:mssqldba-ezmlmshield-x148120.[Email address protected]
Sent: Friday, August 27, 2004 5:47 PM
To: LazyDBA Discussion
Subject: Help with a script to KILL spids.
Hi,
I am attempting to create a script that will traverse the
master.dbo.sysprocesses table, to identify a table named "UserDB" then
kill any processes using this database. First I create a temp table,
then I use a while statement to run through the table identifying which
spid's to kill. For testing purposes, I commented out the Exec
(@Sqlstr) line.
Any suggestions on how to make this script work and possibly optimize it
would be greatly appreciated.
Thanks!
Anthony
Script:
------------------------------------------------------------------------
----------
CREATE TABLE #sys_processes_temp (
spid smallint, --The system process ID.
ecid smallint, --The execution context ID of
a given thread associated with a specific SPID.
status nchar(30), --The process status.
loginame nchar(128), --The login name associated with
the particular process.
hostname nchar(128), --The host or computer name for each
process.
dbname nchar(128), --The database used by the process.
cmd nchar(16)
)
DECLARE @sql VARCHAR(200)
SET @sql = 'select spid, ecid, status, loginame = rtrim(loginame),
hostname, dbname = case when dbid = 0 then null when dbid <> 0 then
db_name(dbid) end, cmd from master.dbo.sysprocesses'
INSERT INTO #sys_processes_temp
EXEC(@sql)
SELECT * FROM #sys_processes_temp
DECLARE @sqlstr VARCHAR(200)
WHILE exists (select spid from #sys_processes_temp where dbname =
'UserDB')
begin
DECLARE @spidno INT
select @spidno = spid from #sys_processes_temp
select @sqlstr = 'KILL ' + convert(varchar(10), @spidno)
print 'Killing spid ' + convert(varchar(10), @spidno)
-- Exec (@Sqlstr)
end
DROP TABLE #sys_processes_temp
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page