Finding Open Cursors (And Their Locks) In SQL Server

If you need information regarding open cursors on your SQL Server, you can query the sys.dm_exec_cursors DMV to get information on when they were created and their various properties. With that DMV along with the sys.dm_exec_sessions DMV and the sys.dm_exec_sql_text DMF to get the SQL that was used to create the cursor.

SELECTc.session_id
,c.cursor_id
,c.properties
,c.creation_time
,c.is_open
,c.fetch_status
,c.dormant_duration
,s.login_time
,t.text
FROM
sys.dm_exec_cursors (0) c
JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(c.sql_handle) t

If you need to see if one of those cursors is holding locks on your system, you can use the sys.dm_tran_locks and sys.dm_exec_sessions DMV 's along with the sys.partitions catalog view to see the cursor and the table it has locked.


SELECTOBJECT_NAME(P.object_id) AS TableName
,L.resource_type
,L.resource_description
,L.request_session_id
,L.request_mode
,L.request_type
,L.request_status
,L.request_reference_count
,L.request_lifetime
,L.request_owner_type
,s.transaction_isolation_level
,s.login_name
,s.login_time
,s.last_request_start_time
,s.last_request_end_time
,s.status
,s.program_name
,s.login_name
,s.nt_user_name
--,c.connect_time
--,c.last_read
--,c.last_write
--,t.text
FROM
sys.dm_tran_locks L
JOIN sys.partitions P
ON L.resource_associated_entity_id = P.hobt_id
JOIN sys.dm_exec_sessions s
ON L.request_session_id = s.session_id
--JOIN sys.dm_exec_connections c
--ON s.session_id = c.most_recent_session_id
--CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE
L.request_owner_type = 'CURSOR'
ORDER
BY L.request_session_id