<div>
That was enough to get me what I needed.<br />
<br />
We discovered this:<br />
<br />
We found the blocking PID from Activity Monitor and stuck it into the query below:<br />
<br />
select sql_handle from sys.dm_exec_cursors(&lt;pid&gt;)<wbr />;<br />
<br />
Then:<br />
<br />
select text from sys.dm_exec_sql_text (&lt;sql_handle&gt;).<br />
<br />
Thanks!
</div>


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!

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


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.

<div>
<div class="content wysiwyg-content">
We have a SQL Server 2005 database where from time to time locks are happening that we have not been able to identify.<br />
<br />
There is a lock being taken sporadically - output from Activity Monitor and sp_who2 identifies the PID that is blocking everything else.<br />
<br />
We turned on Profiler and for that PID we can see that it's executing sp_cursorfetch. &nbsp;We don't know that SQL that's executing in that cursor and would like to know if there's someway to find out.<br />
<br />
Oracle has a couple of dynamic views v$sql and v$sqlarea that can show you the parsed SQL stored in memory. &nbsp;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 ?
</div>
</div>


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 ?

How do I determine query executing in sp_cursorfetch ?

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.