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?

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

First run this to find the offending database.....

SELECT DB_NAME(dbid) as 'Database Name', 
COUNT(dbid) as 'Total Connections' 
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid
SELECT @@MAX_CONNECTIONS AS 'Max Allowed Connections'

Then run this to kill the connections to the desired DB

USE master
go

DECLARE @dbname sysname

SET @dbname = 'Events'

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END