Backup and restore are long running tasks in SQL Server with limited insights into progress. Often a question asked is "How much longer will it take for this operation to complete?". Though there are trace flags and DMVs that provide some information, they are either unstructured (ex: trace flags send output to error logs and can be hard to parse) or hard to interpret.
In SQL Server 2016 (starting with CTP 2), we have introduced a new extended event that can be used to easily gain insight into progress of any of these long running activities. In addition, you can leverage the rich collection and diagnostic capabilities of extended events for advanced analysis. We hope that this extended event will simply your troubleshooting experience with backup and restore activities.
Single Extended Event
You can turn on the backup_restore_progress_trace extended event to trace both Backup and Restore progress.
CREATE EVENT SESSION [Backup trace] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file(SET filename=N'Backup trace')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
The event has the following data that is part of the payload.
name
type_name description
operation_type database_operation_type Type of operation - Indicates whether the database is being backed up or restored
trace_level backup_restore_trace_level Backup/Restore trace level
database_name unicode_string Logical name of the database
trace_message unicode_string Progress trace messages for key steps in backup or restore
name map_key map_value
database_operation_type 0 Backup
database_operation_type 1 Restore
backup_restore_trace_level 0 Information of major steps in the operation
backup_restore_trace_level 1 Verbose I/O related information
Sample Output from a backup process with key information and phases highlighted:
Sample Output from a restore process with key information and phases highlighted:
You can get the latest SQL Server 2016 CTP bits here to play with this feature.
Ajay Jagannathan (
@ajaymsft
)
Principal Program Manager