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.
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.