New extended events for database recovery progress

In yesterday's blog post, 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.

database_recovery_progress_report

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.

Recovery phase in the extended event payload can be one of the following:

PreRecovery
Analysis
Redo
Undo
Complete
PostRecovery

Here is a sample output showing the progress and time estimates for various phases.

database_recovery_times

With this extended event, you can also get the recovery time for specific steps during database startup.

database_recovery_trace

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.

CREATE
EVENT
SESSION [recovery_trace] ON
SERVER

ADD
EVENT sqlserver.database_recovery_progress_report(SET collect_database_name=(1)),
ADD
EVENT sqlserver.database_recovery_times,
ADD
EVENT sqlserver.database_recovery_trace
ADD
TARGET package0.event_file(SET
filename=N'recovery_trace')
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)
GO

You can get the latest SQL Server 2016 CTP bits here to play with this feature.

Ajay Jagannathan (@ajaymsft)
Principal Program Manager