SQL Server Administration: Query to check backup and restore progress. Sometimes backups or restores can take a while to complete. If an automated maintenance job is doing the work, you don't have access to the percent complete progress bar that is part of the SQL Server Management Studio GUI. Using the query you can check the backup or restore percent complete and will get an estimated time that it will complete.
SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete,
dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in('BACKUP DATABASE','RESTORE DATABASE','BACKUP LOG','RESTORE LOG')
SQLRx has worked hard to minimize the impact of monitoring SQL Servers to the point that we are able to monitor high transaction systems with little impact (1% load) on the target system. Contact us today to help you monitor your SQL servers!
|
