DBAtlas · Database operations atlas

See backup / restore / CHECKDB progress and estimated completion

SQL ServerBackup & Log

Command

SELECT r.session_id, r.command, DB_NAME(r.database_id) AS db,
       CAST(r.percent_complete AS DECIMAL(5,1))                     AS pct,
       DATEADD(SECOND, r.estimated_completion_time / 1000, GETDATE()) AS eta,
       r.total_elapsed_time / 60000                                 AS elapsed_min,
       r.wait_type, r.wait_time, r.blocking_session_id
FROM   sys.dm_exec_requests r
WHERE  r.percent_complete > 0;

-- Restore phases and throughput are in the error log:
EXEC sys.xp_readerrorlog 0, 1, N'Restore';

What it does

Shows percentage progress, estimated finish time and current wait for long operations that report percent_complete (BACKUP, RESTORE, DBCC CHECKDB/SHRINK, ALTER INDEX REORGANIZE, rollback after KILL).

When to use it

When the nightly backup runs into business hours, to answer "when will it finish" during a large restore, or to follow the rollback of a killed session.

Caution

The estimate assumes linear progress; during the redo/undo phases of a restore and during log zero-initialization the percentage can stall for a long time. Log files do not benefit from instant file initialization (except growths up to 64 MB on 2022); restores with large logs spend a long time in the first phase. Read-only query; safe.

Open in DBAtlas →

Related