Shulou(Shulou.com)05/31 Report--

How to view unreleased cursors in SQL Server? for this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

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

DECLARE Cursor_Test CURSOR FORSELECT * FROM dbo.TEST;OPEN Cursor_Test

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

SELECT * FROM sys.dm_exec_cursors (0)

For an explanation of the fields of the table returned by this dynamic management function, you can refer to the MSDN documentation. Different database versions are different. If you want to query cursors that are not closed, you need to filter the field is_open (specifying whether the cursor is open or not. 1 for on, 0 for off)

Session 2: view cursors that are not closed

SELECT session_id, cursor_id, name, creation_time, is_openFROM sys.dm_exec_cursors (0) WHERE is_open = 1

If you want to view cursors that are closed but not released, you can execute the close cursor command CLOSE Cursor_Test; in session 1 without releasing the cursor, as shown in the screenshot below

In addition, you can use the following SQL to view the information of cursors on the server that have been open for more than a specified time (1 hour), and 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 This is the answer to GO's question about how to view unreleased cursors in SQL Server. I hope the above content can be of some help to you. If you still have a lot of questions to solve, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.