Mate conexões com vazamento no SQL Server 2005

Question

Nós temos um aplicativo ASP legado que em algum lugar vaza o SQL Connections. No Activity Monitor, vejo vários processos inativos com tempos de último lote com mais de uma hora.

Quando observo o lote de comandos T-SQL, eles são sempre FETCH API_CURSOR [XXX] ([XXX] é um número hexadecimal aleatoriamente ex. FETCH API_CURSOR0000000002CE0BEC), que, no meu entender, é causado por um ASP ADO incorretamente fechado Conjuntos de registros.

Enquanto estamos tentando identificar o código incorreto, existe uma maneira de monitorar quais solicitações abrem quais cursores? Estou assumindo profiler, mas não tenho certeza do que devo monitorar exatamente. Posso ver um monte de chamadas para sp_cursoropen, mas não vejo o nome API_CUSROR [XXX] em lugar algum.

Segundo, alguém poderia sugerir um script que pudéssemos executar para eliminar esses processos com base na hora do último lote > 10 minutos e o último comando em lote sendo FETCH API_CURSOR [XXX]?

Por vários motivos, infelizmente não temos DBAs do SQL Server.

UPDATE

Baseado no script que o jl forneceu e algumas informações que encontrei em SQLAuthority.com , eu criei este script que faz o trabalho muito bem.

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 

No entanto, ainda não sei como corresponder os comandos sp_opencusror ao API_CURSOR [XXX] correspondente usando o profiler.

    

Answer 1

Não é um script original, mas sim ajustado, que pode chegar onde você precisa estar ou pelo menos começar:

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

**** Mais uma vez, não é original, então deve ser dado crédito a esse roteirista agora desconhecido ****

Eu uso uma versão dele para eliminar spids do SQL 2005 que se conectam a um banco de dados de reserva enviado por log usando o Mgt Studion