Hello there, today in this article we are going to find out how to check the reason for database restore Stuck at 100 Percent. DBAs often get the question “how much time it will take for the database to restore? ”
Generally DBAs provide a rough estimation based on the size of the database or by checking prior restore time. But while restoring, it may take more time due to different reasons. You as a DBA need to know how to find out the exact reason and let customer know.
Best way to trace it using Extended event session. There are multiple ways of creating extended event sessions. I have used “Backup_restore_progress_trace” which documents the internal steps of the restore process. You can use the below query to create the extended event.
Query to Create Restore Extended Event
CREATE EVENT SESSION [Restore Trace] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace(
ACTION(sqlos.task_time,sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\Restore Trace.xel') --Change the path as required
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
Now navigate to Extended Events > Expand Sessions > Right Click on the newly created Ex event and click on Start Session:
After this you are ready to start database restore. Once the restore is started you can come back and click on “View Target Data” as shown below. Straightaway to the right you will be able to see the default view.
Now in order to analyze the data, you need to add few columns to the view. Especially “database_Name” “operation_type” and “trace_message”.
For example refer the below screenshot to add “trace_message” to the table, highlight the row > Right Click > click “Show Column in Table”:
Now you can see the there are a number of steps in this process.
The below screenshot shows 100% completion step. Important to realize here is 100% processed bytes is the same amount of data to be transferred from backup file as shown in Fig:4. Moreover you can see that VLF processing continues after that.
Now you know why restore status can stuck/take longer even though it shows 100% completion.
