Inventory Open Cursors per Session with sys.dm_exec_cursors

calendar Nov 3, 2026 / · 9 min read · sys.dm_exec_cursors sys.dm_exec_sessions sys.dm_exec_sql_text sys.dm_exec_requests sys.dm_exec_xml_handles sql server cursors dmv monitoring tsql tempdb  ·
Share on: linkedin copy

If code declares a GLOBAL cursor and never deallocates it, the cursor stays allocated until the connection closes. While it stays open it can hold tempdb space, row locks or both. The collector query below snapshots every cursor on the instance with its properties, state, I/O and idle time, and a short follow-up query narrows the list to cursors more than 36 hours old with the session ID and login that own them.

Purpose and Overview

sys.dm_exec_cursors returns information about the cursors open in the databases on an instance. It is a function, so it takes an argument. Passing 0 returns every cursor for all sessions, and passing a session_id limits the output to that one session.

The main script is the cursor block from an open-source SQL Server monitoring collector. It calls the function once for all sessions and keeps nearly every column it returns: the cursor name, its properties string, the sql_handle and statement offsets of the declaring batch, a UTC creation time, the open and population flags, the fetch buffer state, CPU and I/O counters and dormant_duration. Each row is stamped with the time of the snapshot, so the output can be inserted into a history table and compared run to run.

A second, shorter query joins the function to sys.dm_exec_sessions to pick up the login name and keeps only cursors created more than 36 hours ago. Together the two answer the questions needed to decide whether a cursor is a leak and who to ask about it: what the cursor is, what it is doing, how long it has been idle, and which login owns it.

sys.dm_exec_cursors applies to SQL Server only, and no minimum release is documented for it. Running it requires VIEW SERVER STATE on the server. On SQL Server 2022 and later the permission is VIEW SERVER PERFORMANCE STATE.

Code Breakdown

The main script is the cursor block of a running-queries collector, which runs when its @CollectCursors switch is set to 1:

 1IF @CollectCursors=1
 2BEGIN
 3	SELECT	@SnapshotDateUTC AS SnapshotDateUTC,
 4			session_id,
 5			name,
 6			properties,
 7			sql_handle,
 8			statement_start_offset,
 9			statement_end_offset,
10			plan_generation_num,
11			DATEADD(mi,@UTCOffset,creation_time) AS creation_time_utc,
12			is_open,
13			is_async_population,
14			is_close_on_commit,
15			fetch_status,
16			fetch_buffer_size,
17			fetch_buffer_start,
18			ansi_position,
19			worker_time,
20			reads,
21			writes,
22			dormant_duration
23	FROM sys.dm_exec_cursors (0) c
24END

The snapshot time and the UTC offset

The block depends on two variables that the collector sets once, at the top of the same file, before any of its queries run:

1DECLARE @UTCOffset INT 
2DECLARE @SnapshotDateUTC DATETIME
3SELECT	@UTCOffset = CAST(ROUND(DATEDIFF(s,GETDATE(),GETUTCDATE())/60.0,0) AS INT),
4		@SnapshotDateUTC = GETUTCDATE()

@UTCOffset is the gap between the server's local clock and UTC, measured in seconds, divided by 60 and rounded to whole minutes. @SnapshotDateUTC is the current UTC time. To run the cursor block on its own in a query window, declare @CollectCursors BIT, set it to 1, and run these lines first.

creation_time is the timestamp when the cursor was created, and the documented age check compares it with GETDATE(), the server's local clock. DATEADD(mi,@UTCOffset,creation_time) shifts it by the local-to-UTC offset, so every row carries a UTC creation time next to the UTC snapshot time. That matters once the output is stored: rows collected from servers in different time zones, or either side of a daylight-saving change, still compare correctly. The age of a cursor at the moment of the snapshot is DATEDIFF(hh, creation_time_utc, SnapshotDateUTC).

The (0) argument

sys.dm_exec_cursors (0) covers every session on the instance, which is what you want for an inventory sweep. Once you have a suspect session from a blocking chain or an activity report, replace 0 with that session's ID. The function then returns only that session's cursors.

The properties string and the flags

The select list keeps almost every column the function returns, and the most useful one is properties. It joins the declaration interface, cursor type, concurrency, scope and nesting level into one string, such as TSQL | Dynamic | Optimistic | Global (0).

PartValues and meaning
InterfaceAPI = declared through ODBC or OLE DB; TSQL = declared with DECLARE CURSOR
TypeKeyset, Dynamic, Fast_Forward, and Snapshot, which covers cursors declared as either Snapshot or STATIC
ConcurrencyRead Only, Scroll Locks or Optimistic
ScopeLocal = local to the batch, stored procedure or trigger; Global = global to the connection

The flag columns add state:

  • is_open shows whether the cursor is open.
  • is_async_population shows whether a background thread is still populating a KEYSET or STATIC cursor.
  • is_close_on_commit = 1 means the cursor closes when the transaction ends.
  • fetch_status holds the last @@FETCH_STATUS value returned for the cursor.

Fetch buffer, CPU and I/O

fetch_buffer_size is 1 for Transact-SQL cursors and can be set higher for API cursors, so a value above 1 is another sign that a cursor came from client code. worker_time is in microseconds, and reads and writes count the cursor's I/O.

fetch_buffer_start reads differently by cursor type. For FAST_FORWARD and DYNAMIC cursors it is 0 when the cursor is not open or sits before the first row, and -1 otherwise. For STATIC and KEYSET cursors it is 0 when the cursor is not open, -1 when it is positioned beyond the last row, and otherwise the row number it is positioned on. On an old STATIC cursor, that row number shows how far through the result set the code got before it stopped fetching. ansi_position is the cursor's position within the fetch buffer.

Idle time

dormant_duration is the milliseconds since the last open or fetch on the cursor started. Age finds long-lived cursors and dormancy finds abandoned ones. A cursor in a long-running batch process can be old and busy, while a leaked one is old and dormant. Because the collector keeps both the creation time and dormant_duration on every row, a single snapshot separates the two.

The declaring batch

sql_handle is the handle to the text of the batch that declared the cursor. It is one of the documented inputs to sys.dm_exec_sql_text, together with handles from sys.dm_exec_requests and sys.dm_exec_xml_handles. Combined with statement_start_offset and statement_end_offset, it narrows the text to the current statement. The returned text is NULL for encrypted objects. plan_generation_num is a sequence number that distinguishes instances of a plan after recompilation. The collector stores the handle and offsets rather than the text itself, which keeps each snapshot row small; the text can be resolved later, while the batch is still cached.

Adding the owner: cursors older than 36 hours

The collector block returns a session_id but nothing about who owns the session. The example in the function's reference page fills that gap and applies an age filter:

1SELECT creation_time, cursor_id, name, c.session_id, login_name
2FROM sys.dm_exec_cursors(0) AS c
3JOIN sys.dm_exec_sessions AS s
4    ON c.session_id = s.session_id
5WHERE DATEDIFF(hh, c.creation_time, GETDATE()) > 36;
6GO

The join adds login_name, which is the SQL Server login name under which the session is currently executing. If code in that session has switched its execution context, the name shows the context in effect when you run the query. That is not necessarily the login that opened the connection. For that login, add s.original_login_name to the select list.

DATEDIFF(hh, c.creation_time, GETDATE()) > 36 measures age. A cursor created 40 hours ago and fetched a second ago still qualifies. A cursor created two hours ago that nobody has touched since does not. Adding AND c.dormant_duration > 3600000 keeps only the old cursors that have also sat idle for an hour, which is the shape of a leak.

Key Benefits and Use Cases

  • Catches code paths that skip DEALLOCATE — a GLOBAL cursor is implicitly deallocated only at disconnect, so long-lived connections keep every one they leak.
  • Separates API cursors from T-SQL ones — the properties prefix shows whether a cursor was declared through ODBC or OLE DB (API) or with DECLARE CURSOR (TSQL).
  • Supports blocking triage — a Scroll Locks concurrency value marks a cursor that locks rows as it reads them.
  • Recovers the DECLARE after the fact — passing sql_handle to sys.dm_exec_sql_text returns the declaring batch while the batch text is still cached, with no Extended Events session set up in advance.

Performance Considerations

  • tempdb use: a STATIC cursor answers every request from a temporary table in tempdb, and a KEYSET cursor builds its keyset table there, so old cursors of either type keep that space in use.
  • Row locks: with SCROLL_LOCKS, SQL Server locks the rows as they are read into the cursor, so positioned updates and deletes made through it later are guaranteed to succeed. Check the concurrency value of any old cursor that turns up during blocking triage.
  • Live state only: the function shows cursors that exist right now, and a GLOBAL cursor disappears when its connection closes, so a leak cleared by a connection close leaves no record.
  • Permission by version: Microsoft lists VIEW SERVER STATE as the permission the function needs, and on SQL Server 2022 and later the requirement is VIEW SERVER PERFORMANCE STATE, the narrower permission.

Practical Tips

  • Filter on idleness — replace the DATEDIFF line with WHERE c.dormant_duration > 3600000 to list cursors that have not been opened or fetched for an hour, whatever their age.
  • Identify the client — add s.program_name and s.host_name to point to the application and workstation, but host_name is supplied by the client and should not be relied on as a security feature.
  • Keep history — schedule the script and insert its output into a table, so cursors that vanished with their connections stay on record.
  • Fix the declaration rather than killing the session — follow CLOSE with DEALLOCATE (CLOSE releases the current result set, DEALLOCATE releases the resources the cursor uses), declare cursors LOCAL, or set ALTER DATABASE [SampleDatabase] SET CURSOR_DEFAULT LOCAL WITH NO_WAIT so declarations without a scope default to local.

Conclusion

The collector block is a snapshot of every cursor on a SQL Server instance, in UTC and ready to store, and the 36-hour query adds who owns the old ones. Run them before deeper session or blocking analysis, then read properties, dormant_duration and the declaring batch for each cursor they turn up.

References

Posts in this series