KILL command to kill all the connections to the specific or required database

V-rajbog
Posted by V-rajbog under Sql Server category on | Points: 40 | Views : 3133
Whenever we want to either delete or alter databases,we need to manually execute KILL command to kill all the connections to the specific or required database.

/** This Sproc can be used to kill all the connections/Sessions to a specific database
Usage: USP_kILLConnections @DBName='master' **/

CREATE PROC USP_kILLConnections
@DBName VARCHAR(MAX)
AS
BEGIN

SET NOCOUNT ON

DECLARE @SpId INT,@DbId INT,@Connects INT,@KillStmt VARCHAR(40)

IF( @DBName='Master')
BEGIN
PRINT'Connections to MASTER database cannot be killed'
GOTO ExitPoint
END
SET @DbId=DB_ID(@DBName)/* Set the Database Name here*/
SELECT @SpId =MIN(SpId),@Connects=COUNT(SpId) FROM MASTER.SYS.SYSPROCESSES (NOLOCK)
WHERE DbId=@DbId

SELECT LogiName,* FROM MASTER.SYS.SYSPROCESSES (NOLOCK) WHERE DbId=@DbId

PRINT 'There are Currently '+ CAST(@Connects AS CHAR(3)) +' Sessions connected to the " '+
DB_NAME(@DbId)+'" Database'

WHILE @SpId IS NOT NULL
BEGIN
SET @KillStmt='KILL '+ CAST(@SpId AS CHAR(3))
EXEC(@KillStmt)
SELECT @SpId =MIN(SpId) FROM MASTER.SYS.SYSPROCESSES (NOLOCK)
WHERE DbId=@DbId AND SpId>@SpId
SELECT @Connects=COUNT(SpId) FROM MASTER.SYS.SYSPROCESSES (NOLOCK) WHERE DbId=@DbId
END

PRINT ' Killed all the Connections to the said Database' + CHAR(13)
SELECT @Connects=COUNT(SpId) FROM MASTER.SYS.SYSPROCESSES (NOLOCK) WHERE DbId=@DbId
PRINT ' The Present Connections to this Database are: ' + CAST(@Connects AS CHAR(3))
SELECT LogiName,* FROM MASTER.SYS.SYSPROCESSES (NOLOCK) WHERE DbId=@DbId
ExitPoint:
PRINT ''
END

Comments or Responses

Login to post response