Sometimes our developers will write a query that uses cursors, but doesn't close them out explicitly. I'm trying to generate a list of active objects in my production database that use cursors but don't explicitly close/deallocate them. To do this I've written a simple statement that does the job, but is extremely slow:

    select distinct name, 
    from   SYS.SQL_MODULES 
       inner join SYS.OBJECTS O 
               on SQL_MODULES.OBJECT_ID = O.OBJECT_ID 
    where  SQL_MODULES.DEFINITION like '%open%'
       and SQL_MODULES.DEFINITION like '%declare % cursor%'
       and ( SQL_MODULES.DEFINITION not like '%close%' 
              or SQL_MODULES.DEFINITION not like '%deallocate%' )

Currently this takes something like 3 minutes to run. Is there a better way to get the information I'm looking for?
Top Answer
meme (imported from SE)
Rather than search all of your stored procedure text for those wildcards, you'd probably be better off looking for cursors that are open, and their associated text. That would likely make it easier to locate cursor names, stored procs, etc. written by forgetful developers.

To test, run this in one window:

    IF OBJECT_ID('tempdb..#commands') IS NOT NULL
             DROP TABLE #commands;
    CREATE TABLE #commands
      ID INT IDENTITY(1, 1)
      Command NVARCHAR(2000)
    DECLARE @CurrentCommand NVARCHAR(2000);
    INSERT INTO #commands ( Command )
        SELECT 'SELECT 1';
    DECLARE result_cursor CURSOR
            SELECT Command
                FROM #commands;
    OPEN result_cursor;
    FETCH NEXT FROM result_cursor INTO @CurrentCommand;
                EXEC (@CurrentCommand);
                FETCH NEXT FROM result_cursor INTO @CurrentCommand;
    --CLOSE result_cursor;
    --DEALLOCATE result_cursor;

Then in another window, run this:

    SELECT dec.session_id, dec.cursor_id,,, dec.creation_time, dec.is_open, dec.is_close_on_commit,
            dec.fetch_status, dec.worker_time, dec.reads, dec.writes, dec.dormant_duration, dest.text
        FROM sys.dm_exec_cursors (0) AS dec
        CROSS APPLY sys.dm_exec_sql_text(dec.sql_handle) AS dest
        WHERE dec.is_open = 1;


You should see the cursor opened and left hanging by the other session here, along with any other open cursors. 

If you need to monitor for these (and you don't have a monitoring tool), you can use the dormant_duration column and poll that DMV with an agent job that will fire off an alert or email when an open cursor is dormant for a certain amount of time. That's in milliseconds.


This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.