Interview question: How can you check the progress of a backup or restore that is running now?
Answer: Query sys.dm_exec_requests from another connection while the operation is active. It can show the command, session, percent complete, elapsed time, and an estimated remaining time.
One reason I started SQLAuthority was to keep scripts a DBA could use in the middle of daily troubleshooting. This one helped during a customer performance engagement. A drive suddenly showed more I/O than expected; the request list revealed that a backup was running. Knowing the operation was active changed the conversation immediately.
SELECT r.session_id,
r.command,
CONVERT(decimal(5, 2), r.percent_complete) AS percent_complete,
r.start_time,
r.total_elapsed_time / 60000.0 AS elapsed_minutes,
r.estimated_completion_time / 60000.0 AS estimated_remaining_minutes,
r.wait_type,
SUBSTRING(t.text,
r.statement_start_offset / 2 + 1,
CASE WHEN r.statement_end_offset = -1
THEN (DATALENGTH(t.text) - r.statement_start_offset) / 2 + 1
ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
END) AS active_statement
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.command LIKE N'BACKUP%'
OR r.command LIKE N'RESTORE%';The statement offsets are byte positions in Unicode text. Dividing by two and adding one gives SUBSTRING its character start position. When the end offset is -1, the query takes the rest of the batch instead of cutting it off at an arbitrary length.
Treat the remaining time as a moving estimate, not a promised finish time. The request disappears when it completes, fails, or is cancelled. If you need to know which outcome occurred, check the backup job or restore messages and the relevant history. Run this against the correct instance with permission to see other sessions.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
14 Comments. Leave new
If you remove the “command like” and just focus on “where percent_complete > 0” you’ll also get info about DBCC commands
Sure. Makes sense.
Dear Pinal Dave
i need to say one thing to appreciate your blog post. they are clear/detailed enough to understand and short enough to read.
Thank you and wish you all the best.
Thanks bt
When I am facing any issues and willing for a solution, then first though is Pinal blog . It very good and best solution we getting from your blog.
wow. that’s nice to hear Shakeel
Any idea on how to target a specific db that is restoring? THat query shows all dbs that are restoring on a db. On a busy test db there could be a dozen or so and I want to target the one that I am restoring whose name I know.
add a where clause like this: WHERE CHARINDEX(‘{yourdatabasename}’, r.command) > 0
Doh! Thank you!
Hi Pinal,
Thanks for the query, this really showed the status of the hours completed of the restore initiated,
with a database sized about more than 1 TB which was compressed to 574 GB. But the query that you shared didn’t show the percentage correctly.
This below given command populated me the percentage completion in float.
select start_time, status, blocking_session_id
, wait_type, wait_time, last_wait_type, wait_resource
, percent_complete, estimated_completion_time
,total_elapsed_time, reads, writes, cpu_time
from sys.dm_exec_requests
where command = ‘RESTORE DATABASE’
Pinal, please help me to understand whether is the right query that I can trust upon on SQL Server 2017 (running on Azure VM).
Thank You,
Regards,
Guruprasad
not sure why there was a difference. Both are using same base DMV. I have some extra calculation which he is not having.
Here is mine, I usually run it to check whether my AGs are in the middle of a sync operation, but it also shows info on running DBCC, BACKUP and RESTORE commands:
SELECT r.session_id
,CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS SessionElapsedMins
,DB_NAME(r.database_id) AS DB
,USER_NAME(r.[user_id]) AS UserName
,r.command AS CurrentCommand
,CONVERT(NUMERIC(6, 2), r.percent_complete) AS CurrentCommandPercentComplete
,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GETDATE()), 20) AS CurrentCommandETA
,CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS CurrentCommandETA_Mins
,CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS CurrentCommandETA_Hours
,CONVERT(VARCHAR(8000), (
SUBSTRING(t.[text], r.statement_start_offset / 2, CASE
WHEN r.statement_end_offset = – 1
THEN 8000
ELSE (r.statement_end_offset – r.statement_start_offset) / 2 + 2
END)
)) AS CurrentCommandStatement
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(r.[sql_handle]) t
WHERE percent_complete 0.00;
Thank you, it is very useful. I have 2.8TB backup and I don’t know when it will complete. It gives me a good picture and I don’t need to put my eyes on it all the time.
We may need an update on the syntax for SQL 2022 – I have a NULL in the percent complete column.