November 12, 2007 at 6:46 pm
SET NOCOUNT ON
DECLARE @LoginName SYSNAME
DECLARE @DatabaseName SYSNAME
DECLARE @NumberOfTimesToLoop INT
-- Time to wait between checks
-- should be between 1 to 599 seconds.
DECLARE @TimeToCheckKillInSec INT
SET @LoginName = 'sa'
SET @DatabaseName = 'master'
SET @NumberOfTimesToLoop = 2
SET @TimeToCheckKillInSec = 3
DECLARE @WaitTimeStr VARCHAR(8)
DECLARE @i INT
DECLARE @sCurrentSPIDToKill VARCHAR(32)
DECLARE @sSPID VARCHAR(32)
DECLARE @ServerVersion NVARCHAR(128)
SET @sSPID = CAST(@@SPID AS VARCHAR(32))
IF OBJECT_ID('tempdb..#utbSPWHO2', 'TABLE') IS NOT NULL
DROP TABLE #utbSPWHO2
CREATE TABLE #utbSPWHO2 (
SPID INT,
[Status] NVARCHAR(128),
LoginName NVARCHAR(128),
HostName NVARCHAR(128),
BlockedBy NVARCHAR(32),
DBName NVARCHAR(128),
Command NVARCHAR(128),
CPUTime NVARCHAR(64),
DiskIO NVARCHAR(64),
LastBatchRunTime NVARCHAR(64),
ProgramName NVARCHAR(512),
SPID2 INT,
RequestID INT)
-- The clustered index uses the
-- current @@SPID to make
-- sure that the index name
-- is unique.
EXEC('CREATE CLUSTERED INDEX
CI_#utbSPWHO2_' + @sSPID + '
ON #utbSPWHO2 (LoginName, DBName, SPID)')
-- sp_who2 is version-sensitive - RequestID is returned
-- for SQL 2K5 and later but not in SQL 2K.
SET @ServerVersion = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128))
-- Get the sp_who2 results.
IF @ServerVersion LIKE '8.%' OR @ServerVersion LIKE '7.%'
INSERT INTO #utbSPWHO2 (SPID, [Status], LoginName, HostName, BlockedBy, DBName, Command, CPUTime, DiskIO, LastBatchRunTime, ProgramName, SPID2)
EXEC sp_who2
ELSE
INSERT INTO #utbSPWHO2 (SPID, [Status], LoginName, HostName, BlockedBy, DBName, Command, CPUTime, DiskIO, LastBatchRunTime, ProgramName, SPID2, RequestID)
EXEC sp_who2
IF NOT EXISTS(
SELECT *
FROM #utbSPWHO2
WHERE LoginName = @LoginName
AND DBName = @DatabaseName)
BEGIN
PRINT('The user is not currently using the DB')
RETURN
END
-- @WaitTimeStr is used in
-- a WAITFOR DELAY command later.
SET @WaitTimeStr = '00:0' + CAST(@TimeToCheckKillInSec/60 AS CHAR(1))
+ CASE WHEN (@TimeToCheckKillInSec%60) < 10
THEN ':0'
ELSE ':'
END
+ CAST(@TimeToCheckKillInSec%60 AS VARCHAR(2))
-- Get the first SPID to kill.
-- HostName is checked as well
-- to make sure we don't kill
-- internal machine processes.
SELECT @sCurrentSPIDToKill = CAST(MIN(SPID) AS VARCHAR(32))
FROM #utbSPWHO2
WHERE LoginName = @LoginName
AND DBName = @DatabaseName
AND RTRIM(LTRIM(HostName)) <> N'.'
AND SPID <> @@SPID
WHILE @sCurrentSPIDToKill IS NOT NULL
BEGIN
-- Launch the KILL command.
EXEC('KILL ' + @sCurrentSPIDToKill)
-- Check if the process was killed
TRUNCATE TABLE #utbSPWHO2
IF @ServerVersion LIKE '8.%' OR @ServerVersion LIKE '7.%'
INSERT INTO #utbSPWHO2 (SPID, [Status], LoginName, HostName, BlockedBy, DBName, Command, CPUTime, DiskIO, LastBatchRunTime, ProgramName, SPID2)
EXEC sp_who2
ELSE
INSERT INTO #utbSPWHO2 (SPID, [Status], LoginName, HostName, BlockedBy, DBName, Command, CPUTime, DiskIO, LastBatchRunTime, ProgramName, SPID2, RequestID)
EXEC sp_who2
SET @i = 1
WHILE EXISTS(
SELECT *
FROM #utbSPWHO2
WHERE LoginName = @LoginName
AND DBName = @DatabaseName
AND SPID = CAST(@sCurrentSPIDToKill AS INT))
AND @i <= @NumberOfTimesToLoop
BEGIN
-- Wait and check if the
-- process was killed again.
WAITFOR DELAY @WaitTimeStr
-- Check if the process was killed
TRUNCATE TABLE #utbSPWHO2
IF @ServerVersion LIKE '8.%' OR @ServerVersion LIKE '7.%'
INSERT INTO #utbSPWHO2 (SPID, [Status], LoginName, HostName, BlockedBy, DBName, Command, CPUTime, DiskIO, LastBatchRunTime, ProgramName, SPID2)
EXEC sp_who2
ELSE
INSERT INTO #utbSPWHO2 (SPID, [Status], LoginName, HostName, BlockedBy, DBName, Command, CPUTime, DiskIO, LastBatchRunTime, ProgramName, SPID2, RequestID)
EXEC sp_who2
SET @i = @i + 1
END
-- Check if loop exited
-- because process was killed
-- or due to too many loop.
IF @i > @NumberOfTimesToLoop
BEGIN
-- Too many loop.
-- Abort and notify the user.
RAISERROR('The script could not kill process id = %s.', 16, 1, @sCurrentSPIDToKill)
RETURN
END
ELSE
PRINT('Process id ' + @sCurrentSPIDToKill + ' was killed successfully')
-- If we got this far then
-- the process was killed
-- and we need to find the
-- next process to kill.
SET @sCurrentSPIDToKill = NULL
SELECT @sCurrentSPIDToKill = CAST(MIN(SPID) AS VARCHAR(32))
FROM #utbSPWHO2
WHERE LoginName = @LoginName
AND DBName = @DatabaseName
AND RTRIM(LTRIM(HostName)) <> N'.'
AND SPID <> @@SPID
END
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply