Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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

share|improve this answer
    
This wasn't working, so I output #tmpUsers, the cmd field for the spids that are problematic shows 'AWAITING COMMAND' rather than the last command they ran. This is a good baseline though, thanks. –  Thierry Brunet May 6 '10 at 18:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.