So Tired !_! 逆水行舟, 不进则退!

1Jul/11

sql server 2005结束sleeping的进程语句

Posted by Nick Xu

SET NOCOUNT ON;
DECLARE @host VARCHAR(50), @login NVARCHAR(128);
SET @host = 'dawoo'; --客户端名称,NULL to kill sessions from all hosts.
SET @login = 'tscn'; --登录名
DECLARE @cmd NVARCHAR(255);
DECLARE @possition INT, @total INT, @selSpid SMALLINT;
DECLARE @spidInfo TABLE
(
[id] INT IDENTITY(1,1),
spid SMALLINT,
loginame NVARCHAR(128)
);
INSERT @spidInfo(spid, loginame)
SELECT session_id, login_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND [status] = 'sleeping' AND
login_name = @login AND [host_name] = COALESCE(@host, [host_name]);
SELECT @total = @@IDENTITY, @selSpid = 0, @possition = 0;
WHILE @possition < @total BEGIN SELECT TOP 1 @selSpid = spid, @possition = [id] FROM @spidInfo WHERE [ID] > @possition
SET @cmd = N'KILL ' + CAST(@selSpid AS NVARCHAR(10));
EXEC sp_executesql @cmd;
PRINT 'SessionId = ' + CAST(@selSpid AS NVARCHAR(10)) + '[' + @login +
'] killed by ' + system_user + ' at ' + CAST(GETDATE() AS VARCHAR(50));
END;
IF (@total = 0)
PRINT 'No sessions owned by user ' + '[' + @login + ']';

Tagged as: , , No Comments
   
site
site