Smart Transaction Log Backup Stats

Parikshit Savjani explains how you can use a new DMV to create smart transaction log backups:

In sys.dm_db_log_stats, you will find a new column log_since_last_log_backup_mb which can be used in your backup script to trigger a transaction log backup when log generated since last backup exceeds a threshold value. With smart transaction log backup, the transaction log backup size would be consistent and predictable avoiding autogrows from transactional burst activity on the database. The resulting pattern from the transaction log backup would be similar to below.

The new sys.dm_db_log_stats DMV looks to be quite useful.

Related Posts

Missing Backup Directory When Trying To Upgrade SQL Server

Lori Brown walks us through the solution to an error she experienced: I was recently performing an in-place upgrade of SQL 2008 R2 to SQL 2014 on one of my client’s servers.  I have done a ton of successful and uneventful in-place upgrades and was surprised when the upgrade failed with the error message:  “Failed to […]

Read More

Creating Timelines With dbatools

Marcin Gminski shows how to pull SQL Agent and backup history out of SQL Server and display it as a visual history timeline: Currently, the output from the following commands is supported: Get-DbaAgentJobHistory Get-DbaBackupHistory You will run the above commands as you would normally do but pipe the output to ConvertTo-DbaTimeline, the same way as you […]

Read More

Categories