SQL Server 2016 Extended Events Detailed Backup Information

Problem

There are several ways to track SQL Server database backup and restore progress
using wait stats, DMVs and trace flags, but SQL Server 2016 provides more insight
using Extended Events which we will take a look at in this tip.

Solution

We can view database backup and restore progress with the
sys.dm_exec_requests
DMV and there also
a few trace flags, such as:

Trace Flag 3014: Information regarding file creation,
padding and more about a backup

Trace Flag 3213: Displays information related to a backup that is running
such as buffer count, total amount of memory used, etc.

With this DMV and Trace Flags we can get some details, but it is difficult to track what things
are occurring while a backup is running especially for large databases where
the backup normally takes a long time to run.

SQL Server 2016 provides more insight using these
new Extended Events:

Clearing differential bitmaps - As we know differential backups use a reference point
of the last full backup using bit differential bitmap. The system will clear the differential bitmap, so the next differential backup will reference this full backup, this will not be case with
a copy-only backup.

Writing a checkpoint and checkpoint is complete - At this point a checkpoint is fired before starting the backup and progress is recorded
on how much time it took for the checkpoint.

Start LSN: 94:2416:2, SERepl LSN:0:0:0 - At this point the starting LSN is noted after the checkpoint

Scanning allocation bitmaps - Now a scan is performed for the allocation of pages
for space calculation purposes and backup size.

Calculating expected size of full data - Performing expected total size of data to be copied.

Summary

This is a great new feature available with SQL Server 2016 where we can view internal backup events and duration
for each event, so we can troubleshooting long running backups. In the next
tip, we will look at the restore events and also see more examples for backup and restore.

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.