In yesterday's blog
, I talked about a new extended event for backup and restore. Continuing with the theme of insights into progress of long running tasks, another area we have invested in is database recovery.
As most of you can attest, there is limited information available during database recovery activities such as Analysis, Redo and Undo phases. I am happy to share that in SQL Server 2016, we have introduced
three new extended events
to help you gain insight into database recovery.
This event can be used to gather high level progress information such as phase, percent_complete and estimated time during database recovery. The following data is available as part of the event.
The phase of recovery.
The percentage of work completed.
Total elpased time in seconds since recovery started.
Estimated remaining time in seconds to complete recovery.
Recovery phase in the extended event payload can be one of the following:
Here is a sample output showing the progress and time estimates for various phases.
With this extended event, you can also get the recovery time for specific steps during database startup.
If the two extended events listed above are not sufficient and you wanted detailed insight, you can also turn on
database_recovery_trace extended event.
Note that this can generate lot of data and use with caution. Off course, you can leverage the filtering capabilities of the extended event framework to limit the collection to a specific database or a specific phase.
Here is a sample output showing recovery statistics that can be useful in troubleshooting long running recovery. Some of the useful information that you can instantly get are:
Number of VLFs
Estimated log size
Number of transactions
Time spent in each phase
Extended Event Session Script
The following session definition was used to collect the events above. Though the session can be launched any time during the middle of a long running recovery to gather insight, you can turn on the startup state for the session to automatically launch at startup in case you want to collect data during server startup when database recovery usually happens.
SESSION [recovery_trace] ON
EVENT sqlserver.database_recovery_progress_report(SET collect_database_name=(1)),
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=3 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
You can get the latest SQL Server 2016 CTP bits
to play with this feature.
Ajay Jagannathan (
Principal Program Manager