Killing all processes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Killing all processes

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-10 : 22:56:49

[code]CREATE PROCEDURE [dbo].[KILL_ALL_PROCESSES]
@DBName VARCHAR(20)
AS
BEGIN
DECLARE @SPid INT
DECLARE MY_CURSOR CURSOR FAST_FORWARD FOR
SELECT SPId FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DBName) AND SPId <> @@SPId
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @SPId
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('KILL ' + @SPId)
FETCH NEXT FROM MY_CURSOR INTO @SPId
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
END[/code]
hehe im new here...hope this are useful...if not please del this post

Hope can help...but advise to wait pros with confirmation...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-10 : 23:42:17

If you just want to kill all connections to a particular database, this is an easy way to do it.


use master
set database MyDatabase offline with rollback immediate
set database MyDatabase online

For your procedure, you might want to change this code:

@DBName VARCHAR(20)

to this so that is can handle any possible database name:

@DBName SYSNAME

CODO ERGO SUM

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-11 : 01:47:43

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129162
Improvement made by MVJ

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources