SQL Server to view the methods for not releasing cursors _mssql

SQL Server cursors have been very cold, and rarely use the cursor inside SQL Server, a few days ago, a netizen asked how to check the database has not released the cursor, feel a little meaning, the test verified a bit, conveniently sorted in this.

Session 1: We simulate an application or script that, after opening the cursor, forgets to close and release the cursor.

How do we check for cursors that are not released in the database at this time? In fact, SQL Server provides a dynamic management function sys.dm_exec_cursors that returns information about cursors that are open in various databases.

For an explanation of the fields of the tables returned by this dynamic management function, refer to the MSDN documentation. Different database editions differ. If you want to query for cursors that are not closed, you need to filter the field Is_open to specify whether the cursor is open. 1 for open, 0 for close)

Session 2: Viewing cursors that are not closed

SELECT 
session_id, 
cursor_id, 
name, 
creation_time, 
is_openfrom 
sys.dm_exec_cursors (0) WHERE 

If you want to see a cursor that has been turned off but not released, you can perform the close Cursor_test command at session 1, but not the cursor, as shown in the screenshot below

In addition, you can use the following SQL to view information on the server that has been open for more than a specified time (1 hours) and to adjust the query conditions as needed.

SELECT creation_time 
, cursor_id 
, name 
, c.session_id 
, login_name 
, C.creation_time 
, C.is_ Openfrom 
sys.dm_exec_cursors (0) as C 
JOIN sys.dm_exec_sessions as s on c.session_id = S.session_idwhere 
DATEDIFF (hh, C.creation_time, GETDATE ()) > 1; Go

The above is a small part of the description of SQL Server to see the release of the cursor method, I hope to help!