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