Friday, March 5, 2010

Easy way to kill sessions of database in SQL 2005

This is easiest way to kill all sessions of database.

USE [Master]
GO

DECLARE @DBName VARCHAR(100)
DECLARE @SPId VARCHAR(7000)
SELECT @DBName = 'DBAdmin'

SELECT @SPId = COALESCE(@SPId,'')+'KILL '+CAST(SPID AS VARCHAR)+'; '
FROM Master..SysProcesses
WHERE DB_NAME(DBId) = @DBName
PRINT @SPId
EXEC(@SPId)
GO

2 comments:

  1. Great info, was just looking for it, thanks buddy!

    ReplyDelete
  2. Thanks prawin for comments.
    You have to just change the DBAdmin to your database name.

    ReplyDelete