Kill Leaking Connections on SQL Server 2005

Question

We have a legacy ASP application that somewhere leaks SQL Connections. In Activity Monitor, I can see a bunch of idle processes with Last Batch times over an hour old.

When I look at the T-SQL command batch, these are always FETCH API_CURSOR[XXX] ([XXX] is a randomly seeming hex number, ex. FETCH API_CURSOR0000000002CE0BEC), which from my understanding is caused by improperly closed ASP ADO Recordsets.

While we are trying to pinpoint the offending code, is there a way for me to monitor which requests open which cursors? I'm assuming profiler, but I'm not sure what I should be monitoring exactly. I can see a bunch of calls to sp_cursoropen but I don't see the API_CUSROR[XXX] name anywhere.

Second, would anyone be able to suggest a script we could run to kill these processes based on the Last Batch time > 10 minutes and Last Batch Command being FETCH API_CURSOR[XXX]?

For various reasons, we unfortunately don't have any SQL Server DBAs.

UPDATE

Based on the script that jl provided and some information I found at SQLAuthority.com, I came up with this script which does the job nicely.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[sp_KillHungADORecordsets] @dbname varchar(50)  
AS

CREATE table #oldSpids
( 
    spid int, 
) 

DECLARE @Now DATETIME
SET @Now = GETDATE()

INSERT INTO #oldSpids  
select spid  
from master.dbo.sysprocesses (nolock)  
where dbid = db_id(@dbname)
and spid > 50 
and DATEDIFF(minute,last_batch,@Now) > 10

DECLARE hungSpids CURSOR FAST_FORWARD 
FOR SELECT spid FROM #oldSpids


DECLARE @spid int 

OPEN hungSpids 

DECLARE @strSQL varchar(255) 
DECLARE @sqlHandle VARBINARY(128)
DECLARE @sqlText VARCHAR(MAX)

FETCH NEXT FROM hungSpids INTO @spid
WHILE (@@fetch_status <> -1) 
BEGIN 
    IF (@@fetch_status <> -2) 
    BEGIN 
        SELECT @sqlHandle = sql_handle
        FROM sys.sysprocesses
        WHERE spid = @spid
        SELECT @sqlText = TEXT
        FROM sys.dm_exec_sql_text(@sqlHandle)
        IF (@sqlText LIKE 'FETCH API_CURSOR%')
        BEGIN
            PRINT 'Killing ' + convert(varchar(10),@spid) 
            SET @strSQL = 'KILL ' + convert(varchar(10),@spid) 
            EXEC (@strSQL) 
        END 
    END
    FETCH NEXT FROM hungSpids INTO @spid 
END 

CLOSE hungSpids 
DEALLOCATE hungSpids 
DROP table #oldSpids 

I still don't know how to match the sp_opencusror commands to the corresponding API_CURSOR[XXX] using profiler, though.

Answer 1

Not an original script but tweaked that might get where you need to be or at least a start:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspKillUsersFETCH] @dbname varchar(50) 
as

DECLARE @strSQL varchar(255)
PRINT 'Killing Users '
PRINT '-------------------------------------------------------------------------------------------'


CREATE table #tmpUsers(
spid int,
dbname varchar(128),
cmd varchar(128))


INSERT INTO #tmpUsers 
select spid, convert(varchar(128),db_name(dbid)), cmd
 from master.dbo.sysprocesses (nolock) 

DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, program_name FROM #tmpUsers WHERE dbname = 'database name here'
and spid > 50
and cmd like '%FETCH API_CURSORXXX%' 


DECLARE @spid int
DECLARE @dbname2 varchar(128)
OPEN LoginCursor

FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
    PRINT 'Killing ' + convert(varchar(10),@spid)
    SET @strSQL = 'KILL ' + convert(varchar(10),@spid)
    EXEC (@strSQL)
    END
    FETCH NEXT FROM LoginCursor INTO  @spid, @dbname2
END

CLOSE LoginCursor
DEALLOCATE LoginCursor

DROP table #tmpUsers

****Again, not original so credit must given to that now-unknown script writer ****

I use a version of this to kill SQL 2005 spids that connect to a log-shipped stand-by database using Mgt Studion