DMV for Log Statistics in SQL Server

There aren’t too many changes to the Dynamic Management Views in SQL Server 2017, but one was mentioned that I worth a second look and often have a lot more going on than upon first look.

Gif Credit: ancsy55.tumbr.com

DM_DB_LOG_STATS

This is an addition to SQL Server 2017 and available in Azure Database/Azure Data Warehouse to simplify and synchronize how we query information about log statistics, not just backups.  The added benefit of this is that it only requires the VIEW DATABASE STATE privilege to utilize it, which eases the demand on anyone who’s tried to grant rights to view backup information to non-DBA personnel in previous releases.  With this DMV, you can monitor, alert and kick off jobs to manage the transaction log backups.

It does require the database_id to be passed to it to provide results, but who keeps that around?  Much easier to just do a Cross Apply to sys.databases and make your life simple.

With the following query template:

SELECT name as 'Database Name',  <values from below> as '<alias for view column>'
FROM sys.databases AS s
CROSS APPLY sys.dm_db_log_stats(s.database_id)
where name='<Database Name>';

updated with various view columns from below, aliases if you prefer and then the database name if you want to specify, you can gather valuable transaction log statistics.

Important View Column Names

recovery_model	SIMPLE, FULL, BULK_LOGGED
log_min_lsn or log_end_lsn	the earliest and latest LSN included
current_vlf_sequence_number	virtual log file sequence number info
current_vlf_size_mb	the size in mb of the virtual log file
total_vlf_count	Number of virtual log files residing in the transaction log
total_log_size_mb	Total size of transaction log in MB
log_backup_time	Time last transaction backup occurred
log_backup_lsn	What is the most recent LSN backed up
log_checkpoint_lsn	What was the last LSN that experienced a checkpoint
log_recovery_size_mb	The size of the logs to be recovered since the last full backup
log_truncation_holdup_reason	Lots of information and can be joined to sys.databases.log.reuse_wait_desc.  The results from here will quickly describe what latency is occurring for the transaction log to be truncated, letting you know of rollback or replication.

What can you do with this query template with the information shown?

What LSNs are in my Transaction Logs?

For those of you learning about this from the Oracle side, the LSN or Log Sequence Number is like an SCN in Oracle.

SELECT name as 'Database Name', log_min_lsn as 'Minimum LSN', log_end_lsn as 'Maximum LSN'
FROM sys.databases AS s
CROSS APPLY sys.dm_db_log_stats(s.database_id)
where name='BaseballData';

This will result in the minimum and maximum LSNs that are currently backed up in my logs for the database for the name given:

Last Backup of the Transaction Log?

Knowledge of backups, both full and log backups are an essential part of, (for on-premises and any IaaS) DBAs job.  We all have our trusty scripts and to produce reports, but there’s been some additions to the DMVs to assist in providing more information in, (what I feel) is a simpler format.

SELECT name AS 'Database Name', log_backup_time AS 'last log backup time' FROM sys.databases AS s CROSS APPLY sys.dm_db_log_stats(s.database_id);

For this example, I took out the addition of a unique database, so you can see that all the databases for this SQL Server were returned and as they are set to “Simple”

I can also monitor the size of the log since the last backup and upon a certain threshold, kick off a backup:

SELECT s.name as 'Database Name', log_since_last_log_backup_mb as 'Log Size Since Last Backup'
FROM sys.databases AS s
CROSS APPLY sys.dm_db_log_stats(s.database_id)
where name='BaseballData';

 

You can also do a small amount of a switch on the template to simply join and pull more refined data from the dm_db_log_stats DMV.  Let’s say we want to return the database name, the recovery model and the last backup time for the transaction log for a single database and we already know what that database_id is:

SELECT t.name as 'Database Name', s.recovery_model as 'Backup Model', s.log_backup_time AS 'last log backup time'
 FROM sys.dm_db_log_stats(5) AS S,sys.databases AS t
 where t.database_id=s.database_id;

As you can see from above, I chose to pass the database_ID to the DMV and then join on sys.databases to pull the database name, but to provide the other column data from the DMV.

Trouble Avoidance

One area of contention for how SQL Server transaction logs perform has to do with the total number of Virtual Log Files, (VLFs) vs. the number of active VLFs.  If there is a hold up in truncating VLFs, (too many are held with a “lock” stopping it from truncating to be reused and the transaction log is required to autogrow, this can create a poor performance scenario.

Due to this, the dm_db_log_stats DMV can be used to monitor the number of VLFs, active VLFs, etc. and then alert when a threshold is reached.  This way the DBA isn’t forced to shrink the transaction log, which could impact overall database performance if it is done regularly, (also a sign of poor code/database design in my opinion.)

SELECT t.name as 'Database Name', s.total_vlf_count as 'Total VLFs', s.active_vlf_count as 'Active VLFs', s.log_truncation_holdup_reason as 'Reason for Log Holdup'
FROM sys.dm_db_log_stats(5) AS S,sys.databases AS t
where t.database_id=s.database_id;

And for my small sample database, there aren’t a lot of VLF’s, (vs. a production system which may have 10K or more) but you get the idea… 🙂