?
Solved

How do I determine query executing in sp_cursorfetch ?

Posted on 2009-12-18
3
Medium Priority
?
696 Views
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 ?
0
Comment
Question by:AddOnsInc
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
jorge_toriz earned 1500 total points
ID: 26085004
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
0
 

Author Comment

by:AddOnsInc
ID: 26110057
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>);

Then:

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

Thanks!
0
 

Author Closing Comment

by:AddOnsInc
ID: 31667822
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.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Copy Database Wizard 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.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

850 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