Q

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?

 

 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

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close