Credits:
Author: ChillyDBA
Date: 3 Sep 2014
Description
Restoring a production database can either be a routine action (when creating or updating development databases) or a problem resolution for a production system. In both cases I've found it to be annoying that there is no built-in mechanism in SQLEM for easily determining how much of the restore process is complete.
This code produces a compact report detailing progress so far and ETA for all database restores on a server. I've used this many times now and have found it to be very accurate.
Code
-- get list of in-progress restores on the server -- plus elasped time and ETA
SELECT r.session_id, r.command, CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete], CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GETDATE()),20) AS [ETA Completion Time], CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], CONVERT(VARCHAR(1000),( SELECT SUBSTRING(TEXT,r.statement_start_offset/2,CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle) ) ) FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')
|
 Updating...
Andy Hughes, Sep 3, 2014, 9:40 AM
|