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.
- sys.dm_exec_cursors
- 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
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips