Ask the Expert

Script that kills all processes for a given database

I am trying to write a script that kills all processes for a given database.
 USE MASTER SELECT SPID FROM SYSPROCESSES P, SYSDATABASES D WHERE P.dbID = D.dbID AND D.NAME = 'DB'
Once I have identified the SPIDs, how do I "loop" through the rows returned and issue the KILL SPID statement?

    Requires Free Membership to View

 

 IF OBJECT_ID('dbo.sp_killalldbusers') IS NOT NULL BEGIN DROP PROCEDURE dbo.sp_killalldbusers IF OBJECT_ID('dbo.sp_killalldbusers') IS NOT NULL PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_killalldbusers >>>' ELSE PRINT '<<< DROPPED PROCEDURE dbo.sp_killalldbusers >>>' END go create procedure sp_killalldbusers @dbName varchar(32) as declare @spid smallint declare @cmd varchar(32) create table #tmp ( spid smallint, status varchar(32), loginame varchar(32), hostname varchar(32), blk char(8), dbname varchar(32), cmd varchar(255)) declare cLogin cursor for select spid from #tmp where dbname = @dbName insert into #tmp exec sp_who open cLogin fetch cLogin into @spid while @@fetch_status = 0 begin select @cmd = 'kill ' + CONVERT(char, @spid) print @cmd execute( @cmd ) fetch cLogin into @spid end close cLogin deallocate cLogin go IF OBJECT_ID('dbo.sp_killalldbusers') IS NOT NULL PRINT '<<< CREATED PROCEDURE dbo.sp_killalldbusers >>>' ELSE PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_killalldbusers >>>' Go

 

For More Information

This was first published in November 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: