SQL Server DBAs use Transaction Log backups for point-in-time recovery of a database in the full recovery model. It truncates the log file and prevents it from growing too large and consuming a lot of disk space. Before SQL Server 2017, these backups were scheduled in specific time intervals, such as every 15/30/60 minutes–based on the business need. In this way, even if the log file had very minimum data, a transaction log backup was issued regardless of the size, because it ran based on time. Due to this, the size of the log backup always varied. Even if someone set up the log backup for every 15 minutes, a long-running data manipulation query could cause the log to grow very fast and generate a lot of autogrow events and ultimately degrade performance.

In SQL Server 2017 a new Dynamic Management Function sys.dm_db_log_stats has been introduced to help get more information on log files.

Some of the important output columns by using this DMF are as follows:

  • recovery_model: provides the current database recovery model i.e. Full, Bulk-logged or Simple.
  • Total_vlf_count: shows total number of vlf file in the log.
  • active_vlf_count: also shows active number of vlf count in the log file.
  • log_truncation_holdup_reason: provides a reason for this which can help to investigate the issue.
  • log_backup_time: backup time of last transaction log backup.
  • log_since_last_log_backup_mb: provides the total size of MB in the log file after the last log backup.
  • total_log_size_mb: total size of transaction log in MB.
  • active_log_size_mb: total size of active transaction log in MB.

We can use the following query to pull up the specified information using this DMF:

SELECT
       database_id,
       recovery_model,
       total_log_size_mb,
       active_log_size_mb,
       total_vlf_count,
       active_vlf_count,
       log_truncation_holdup_reason,
       log_backup_time,
       log_since_last_log_backup_mb
FROM   sys.Dm_db_log_stats(Database_id)

Output
OUTPUT

Monitoring Virtual Log Files(VLFs)

From the above output we see columns called total_vlf_count and active_vlf_count, these allow monitoring and alert you if it exceeds a certain threshold. When the active_vlf_count reaches the total_vlf_count it issues an autogrow event and degrades performance. We can take a look at log_truncation_holdup_reason and act accordingly. These VLF counts can be captured by a scheduled job periodically to monitor the health of the transaction log file.

Using this DMF, we can set a threshold and issue a transaction log backup to avoid active VLFs from growing larger. This keeps the total VLFs constant and avoids the autogrow.

Configuring Tlog backup based on size

By using this DMF we can configure the Transaction Log backup job based on the log_since_last_log_backup_mb column. We can issue Transaction log backup when the log file reaches a certain size instead of a periodic interval.

As example: In the past, we would configure log backups every hour, every 15 minutes or some other specific time. Even if there was not changes to the data, the transaction log will be backed up. Fortunately with the use of this DMF, we can now configure log backup when it reaches 1GB in size, 500MB and so on. That way it can avoid scheduled time and work based on data load.

Note: You must keep in mind if a transactional log backup is not issued for couple of hours or longer due to size limitation, there is a potential for data loss after the last transaction log backup in the case of a disaster.