Overview of SQL Server Table-Valued Functions sys.dm_exec_cursors and sys.dm_db_log_info

By: Jayendra Viswanathan   |   Comments   |   Related: > Functions System

Problem

Functions are objects that return values after some process and SQL Server offers many system functions that are used internally and things that we can use as well.  There are table-valued functions and scalar functions and in this tip we will take a look at a couple of these to get you started.

Below is a list of some of the table-valued functions that can be found in the master database.  In this tip we are going to look at sys.dm_exec_cursors and sys.dm_db_log_info.

Solution

In this tip we will take a look at two system table-valued functions that you can use with SQL Server.

1. sys.dm_exec_cursors
2. sys.dm_db_log_info

Review of SQL Server function sys.dm_exec_cursors

The sys.dm_exec_cursors function returns details of cursors from all databases. This was released in SQL Server 2008.

Let’s create a table MyApp with two columns. We'll open two cursors with the FETCH statement and see how the dm_exec_cursors will help us.

DROP TABLE if exists MyApp
 
CREATE TABLE MyApp
(
   AppNum  NUMERIC(8) not null,
   AppName VARCHAR(40) NOT NULL,
)
 
IF CURSOR_STATUS('global','Mycur1')>=-1
BEGIN
   CLOSE Mycur1
   DEALLOCATE Mycur1
END
 
DECLARE Mycur1 CURSOR
FOR SELECT Appname  FROM MyApp
OPEN Mycur1

FETCH NEXT FROM Mycur1
 
IF CURSOR_STATUS('global','Mycur2')>=-1
BEGIN
   CLOSE mycur2
   DEALLOCATE Mycur2
END

DECLARE Mycur2 CURSOR
FOR SELECT Appname  FROM MyApp
OPEN Mycur2

FETCH NEXT FROM Mycur2
			

Let’s fetch information for the created cursors. The sys.dm_exec_cursors function accepts a parameter for session_ID to return for the specified session. If 0 is passed, it will retrieve cursor details from all sessions.

SELECT * FROM sys.dm_exec_cursors(0)			

Here is the partial output.

Brief output explanation:

• session_id - stores the ID of the session that holds the cursor.
• cursor_id - stores the ID of the cursor object.
• name - stores the cursor name that was created.
• properties:
  • TSQL – is used as creating interface.
  • Dynamic – is the cursor type.
  • Optimistic – is the concurrency of the cursor.
  • Global (0) - is the scope of the cursor.
• sql_handle: Handle to the text of the batch that declared the cursor.
• There are additional columns, so take a look at this for more info about the additional columns.

We can also join the sys.dm_exec_cursors and sys.dm_exec_sessions tables and get more details.

SELECT name, c.session_id, c.creation_time, c.is_open, host_name, nt_domain,nt_user_name, creation_time, cursor_id
FROM sys.dm_exec_cursors(0) AS c   
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id   
			

Here we can see the output that provides more useful information.

The sys.dm_exec_cursors function returns information about the cursors that are open in any database and you will need VIEW DATABASE STATE permissions in the database.

Review of SQL Server function sys.dm_db_log_info

The sys.dm_db_log_info table-valued function returns Virtual Log File (VLF) table details of the database. This function was released in SQL Server 2016 SP2.  Let's see how we can use the function.

The function accepts a parameter which is the database ID. You may have many databases on your server, so we can run a simple query to get the database_id as shown below.

SELECT name, database_id FROM sys.databases			

From the output let's use database_id = 5 output for the sys.dm_db_log_info function.

SELECT * FROM sys.dm_db_log_info(5)			

Below we can see the output.  We can see that there are 12 virtual log files for the FamilyDB database.  We can also see the size for each VLF in MB.  This is helpful to see how many times the log file had to grow and the size of each increment. 

The sys.dm_db_log_info function needs VIEW DATABASE STATE permissions in the database.

To get the current database VFL details, we can use DB_ID() as a parameter to get the VFL details of current database.

USE FamilyDB
GO

SELECT * FROM sys.dm_db_log_info(db_id())			

Also, we could query sys.databases and sys.dm_db_log_info for a specific database as follows:

USE FamilyDB
GO

SELECT d.name, d.database_id, d.recovery_model_desc, li.file_id, li.vlf_begin_offset, li.vlf_size_mb, li.vlf_active, li.vlf_status 
FROM sys.dm_db_log_info(db_id()) li	
INNER JOIN sys.databases d ON li.database_id = d.database_id

If we want to get information for all databases, we could use a query like this, using CROSS APPLY to fill the function value with the database_id.

SELECT d.name, d.database_id, d.recovery_model_desc, li.file_id, li.vlf_begin_offset, li.vlf_size_mb, li.vlf_active, li.vlf_status 
FROM sys.databases d
CROSS APPLY sys.dm_db_log_info(d.database_id) li	

Summary

There are many table-valued system functions available in SQL Server which can be useful for our day to day work.  Take some time to explore them as shown below to see what can help you better manage SQL Server.

Next Steps

• Check out these other tips on system functions

About the author

Jayendra is a Project Leader with many years of IT experience. He has strong knowledge in software development and project management.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips