Killing User Connection(session) connected to the Database.

,

Copy and Paste the script to the SSMS query window. Navigate to the database and execute.

You can use the script in the job step also which you can use to refresh the database for repeated refresh of database requests.

DECLARE @SPID varchar(8000)
DECLARE @Conntions_Killed smallint
DECLARE @DBName varchar(100)

SET @SPID = ''
SET @Conntions_Killed = 0;
SET @DBName = 'test' -- Pass the Database Name.

SELECT @SPID=coalesce(@spid,',' )+'KILL '+convert(varchar, spid)+ '; '
FROM MASTER..SYSPROCESSES WHERE dbid=db_id(@DBName);

Print @SPID;

IF LEN(@SPID) > 0
BEGIN
EXEC(@SPID);

SELECT @Conntions_Killed = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

END

Rate

3.5 (2)

Share

Share

Rate

3.5 (2)