sql-server - SQL Server 2005 - 关闭 sleep 连接

Question

我的开发服务器上似乎有一个应用程序有很多打开的连接(它们应该在那里，但是一些错误的数据层被用来打开它们，忘记关闭它们)。我只想关闭它们，这样我就可以让其他应用程序在服务器上运行。如何强制关闭所有连接？

Answer 1

使用以下脚本终止来自特定主机/登录的非事件 session 。您可以从计划的工作中使用它，当然您的首要任务应该是修复您的应用程序层。

SET NOCOUNT ON;

DECLARE @host VARCHAR(50), @login NVARCHAR(128);

SET @host = 'fooHost'; --NULL to kill sessions from all hosts.
SET @login = 'fooLogin';

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 + ']';