How do I determine query executing in sp_cursorfetch ?

Posted on 2009-12-18
Last Modified: 2012-05-08
We have a SQL Server 2005 database where from time to time locks are happening that we have not been able to identify.

There is a lock being taken sporadically - output from Activity Monitor and sp_who2 identifies the PID that is blocking everything else.

We turned on Profiler and for that PID we can see that it's executing sp_cursorfetch.  We don't know that SQL that's executing in that cursor and would like to know if there's someway to find out.

Oracle has a couple of dynamic views v$sql and v$sqlarea that can show you the parsed SQL stored in memory.  Does SQL Server offer something similar so that we can determine what's executing this cursor that seems to be randomly taking control of our DB ?
Question by:AddOnsInc
    LVL 15

    Accepted Solution

    If you have the sql_handle (the unique identifier for each unique sql statement executed on the server) you can use the dm_exec_sql_text DMF

    Author Comment

    That was enough to get me what I needed.

    We discovered this:

    We found the blocking PID from Activity Monitor and stuck it into the query below:

    select sql_handle from sys.dm_exec_cursors(<pid>);


    select text from sys.dm_exec_sql_text (<sql_handle>).


    Author Closing Comment

    The solution as posted gave a pointer in the right direction, we had to work out the rest ourselves.  So, a B for the pointer that wasn't quite the complete solution in and of itself.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now