September 5, 2013 at 2:37 am
Comments posted to this topic are about the item Kill User Processes Per Database or Server Wide
September 5, 2013 at 5:54 am
If a login is connected to the database multiple times, it only kills the newest spid.
We have applications that uses sqllogins and there may be several users connected to the database
with the same sqllogin with different spids. Here's the updated script that will handle the multiple
spid for the same login.
USE [MASTER]
GO
SET NOCOUNT ON;
DECLARE @DatabaseOrServerWide CHAR(1)
DECLARE @Cursor CURSOR
DECLARE @dbid INT
DECLARE @ProcID INT
DECLARE @User VARCHAR(100)
DECLARE @DatabaseName VARCHAR(200)
DECLARE @info VARCHAR(10)
DECLARE @ProcessDetails TABLE
(
ProcessID INT,
LoginName VARCHAR(200)
)
--Kill all server processes or just process for a specific database:
SET @DatabaseOrServerWide = 'D' -- D = Database, S = Server
--If database only, set database name:
SET @DatabaseName = 'DCO88001'
--Get process details for specified database
IF @DatabaseOrServerWide = 'D'
BEGIN
SELECT @dbid = [dbid] FROM sys.sysdatabases WHERE [name] = @DatabaseName
IF @dbid IS NULL
BEGIN
PRINT @DatabaseName + ' not found on current SQL instance.'
GOTO EndProcess
END
INSERT INTO @ProcessDetails
SELECT
SPID,
[loginame]
FROM
sys.sysprocesses
WHERE
[dbid] = @dbid
AND [loginame] != ''
AND [loginame] != 'sa'
AND [spid] != @@SPID
IF (SELECT COUNT(0) FROM @ProcessDetails) = 0
BEGIN
PRINT 'No users currently connected to ' + @DatabaseName + ' excluding sa processes and this thread.'
GOTO EndProcess
END
END
--Get process details for entire instance
IF @DatabaseOrServerWide = 'S'
BEGIN
INSERT INTO @ProcessDetails
SELECT
SPID,
[loginame]
FROM
sys.sysprocesses
WHERE
[loginame] != ''
AND [loginame] != 'sa'
AND [spid] != @@SPID
IF (SELECT COUNT(0) FROM @ProcessDetails) = 0
BEGIN
PRINT 'No users currently connected to instance, excluding sa processes and this thread.'
GOTO EndProcess
END
END
--Kill processes
SET @Cursor = CURSOR FOR
SELECT
ProcessID,
LoginName
FROM
@ProcessDetails
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO
@ProcID,
@User
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC('KILL ' + @ProcID)
PRINT 'Process killed from login: ' + @User
PRINT @PROCID
FETCH NEXT FROM @Cursor INTO
@ProcID,
@User
END
CLOSE @Cursor
DEALLOCATE @Cursor
--End information
EndProcess:
SELECT @info = COUNT(0) FROM @ProcessDetails
PRINT ''
PRINT @info + ' processes killed.'
PRINT ''
PRINT 'Script End'
September 5, 2013 at 9:22 am
If you want to kill all connections to a single database, simply run:
USE [master]
GO
ALTER DATABASE <db_name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE <db_name> SET MULTI_USER
GO
April 27, 2016 at 4:50 pm
Thanks for the script.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply