I use the following SP. Simply put the name of the DB in as a parameter.
example:
exec sp_KillProc 'databasename'
CREATE proc sp_killproc
(@dbname varchar(20))
as
Declare @dbid int,
@spid int,
@str nvarchar(128)
select @dbid = dbid from master..sysdatabases
where name = @dbname
declare spidcurs cursor for
select spid from master..sysprocesses where dbid = @dbid
open spidcurs
fetch next from spidcurs into @spid
While @@fetch_status = 0
Begin
Select @str = 'Kill '+convert(nvarchar(30),@spid)
exec(@str)
--print @str
fetch next from spidcurs into @spid
End
Deallocate spidcurs
GO
Thanks,
Tom Whitfield
Senior Support Engineer
Magic Service Desk
Remedy, a BMC Software company
-----Original Message-----
From: Thomas Anthony
[mailto:mssqldba-ezmlmshield-x66391310.[Email address protected]
Sent: Monday, August 30, 2004 7:42 AM
To: LazyDBA Discussion
Subject: RE: Help with a script to KILL spids.
If you are running SQL Server 2000, just issue an ALTER DATABASE
statement; it's quicker.
USE master
GO
ALTER DATABASE UserDB
SET OFFLINE
WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE UserDB
SET ONLINE
GO
Sincerely,
Anthony Thomas, MCDBA, MCSA
-----Original Message-----
From: Jake Smith
[mailto:mssqldba-ezmlmshield-x74810575.[Email address protected]
Sent: Friday, August 27, 2004 7:55 PM
To: LazyDBA Discussion
Subject: RE: Help with a script to KILL spids.
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]
---------------------------------------------------------------------
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]
***********************************************************************
NOTICE: This electronic mail message and any attached files are
confidential. The information is exclusively for the use of the
individual or entity intended as the recipient. If you are not the
intended recipient, any use, copying, printing, reviewing, retention,
disclosure, distribution or forwarding of the message or any attached
file is not authorized and is strictly prohibited. If you have received
this electronic mail message in error, please advise the sender by reply
electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your
computer system.
***********************************************************************
---------------------------------------------------------------------
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