Activity Monitor - 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

SQL Server 2005 Forums

SQL Server Administration (2005)

Activity Monitor

Author

Topic

ratheeshknair
Posting Yak Master

129 Posts

Posted - 2008-04-23 : 04:23:19

Hi experts,
I just want to know how can i kill all the processes of a database
if the database have more than 100 connections.This is for the purpose of restoring a database.

RKNAIR

GunterKDR
Starting Member

5 Posts

Posted - 2008-04-23 : 08:36:48

Try...

use master
go

IF EXISTS ( SELECT * FROM sys.procedures WHERE name = 'sp_kill_Camra_Connections' )
DROP PROC sp_kill_Camra_Connections
go

CREATE PROC sp_kill_Camra_Connections
@db_Name sysname = NULL

AS
BEGIN

IF @db_Name IS NULL
RETURN 0

DECLARE @z INT,
@maxIt INT,
@s varchar( 2000 )

SELECT @maxIt = 5
CREATE TABLE #SPIDS ( s INT )

INSERT INTO #SPIDS
SELECT DISTINCT p.spid
FROM master..sysprocesses as p
where p.dbid = convert( varchar, db_ID( @db_Name ) )

IF NOT EXISTS ( select * FROM #SPIDS )
RETURN 0

HELL:
SELECT @maxIt = @maxIt - 1

DECLARE cU INSENSITIVE CURSOR
FOR
SELECT s FROM #SPIDS

OPEN CU

FETCH NEXT FROM cu INTO @z

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @s = 'kill ' + convert( varchar, @z )
PRINT @s
EXEC ( @s)

FETCH NEXT FROM cU INTO @z
END

CLOSE cu
DEALLOCATE cu

WAITFOR DELAY '00:00:02'

TRUNCATE TABLE #SPIDS

INSERT INTO #SPIDS
SELECT DISTINCT p.spid
FROM master..sysprocesses as p
where p.dbid = convert( varchar, db_ID( @db_Name ) )

IF EXISTS ( select * FROM #SPIDS ) AND @maxIt > 0 GOTO HELL
ELSE IF EXISTS ( select * FROM #SPIDS )
BEGIN
DECLARE cU INSENSITIVE CURSOR
FOR
SELECT s FROM #SPIDS

OPEN CU
FETCH NEXT FROM cu INTO @z

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @s = 'EXEC sp_who ' + convert( varchar, @z )
EXEC ( @s)

FETCH NEXT FROM cU INTO @z
END

CLOSE cu
DEALLOCATE cu
END

DROP TABLE #SPIDS
END
GO

Good Morning-
Gunter

They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety. -- Benjamin Franklin

ratheeshknair
Posting Yak Master

129 Posts

Posted - 2008-04-23 : 11:14:25

Thanks GunterKDR

RKNAIR

gwhiz
Yak Posting Veteran

78 Posts

Posted - 2008-04-23 : 15:17:41

One of the ways I do this is to run an ALTER DATABASE databasename SET SINGLE_USER WITH ROLLBACK_IMMEDIATE.

This will immediately kick everyone out of the database allowing you to restore. I find this alternative works better than killing the SPIDS especially if you are doing this in a automated fashion as some times killing a SPID initiates a long rollback process

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources