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

Backups With Checksum Validation

Eric Blinn shows the upside to performing checksum validation during backups: Since a full backup reads every data page it makes sense that we can ask SQL Server to calculate and compare each checksum during this operation. Even a differential backup reads all of the pages for any extent (a group of 8 data pages) […]

Read More

Database Backups With dbatools

Garry Bargsley continues the 12 Days of dbatools series, this time taking a look at taking backups: We are on the home stretch and I have saved the last third of the series to cover some pretty cool/amazing commands.  Not that all 500+ commands are not cool/amazing but these last few are used continuously in […]

Read More

Categories