SQL Server 2005 - closing sleeping connections

Question

I seem to have an app on my Dev server that has lots of open connections (they should be there, but some bad data layer was used to open them, that forgot to close them). I just want them closed so I can keep other apps running on the server. How can I force all the connections to close?

Solution 1

Use the following script to kill inactive sessions from a specific host / login. You could use it from a scheduled job, of course your priority should be to fix your app tier.

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

Solution 2

Other than killing your connections manually, you can

• Dispose of the connections. That is, retrieve the Connection objects somehow and call .Close and .Dispose() on them. Using "using" would be ideal since it calls .Dispose() automatically for you.
• Recycle your application pool.

Solution 3

Use the last_batch column from sysprocesses to work out if it's really active or not. SPID > 50 (or is it >= 50?) to avoid killing system SPIDs.

Compare this to your desired sleep time and KILL spid.

You'll have to loop through.

DECLARE @kill_id smallint 
DECLARE spid_cursor CURSOR FOR
select spid from sysprocesses 
where dbid = > 4 and last_batch < dateadd(hour, -24, getdate()) and spid >= 50

OPEN spid_cursor

FETCH NEXT FROM spid_cursor INTO @kill_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Kill the current spid here
-- KILL @kill_id <---This line will not work

-- Get the next cursor row
FETCH NEXT FROM spid_cursor INTO @kill_id
END 

CLOSE spid_cursor

DEALLOCATE spid_cursor