How can we improve SQL Backup?

Show estimated time to completion

Would be great to see estimated time to completion on the In Progress tab during a restore

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(6,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(100),(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')

14 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    @SQL4GNT shared this idea  ·   ·  Admin →

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Ray commented  · 

        The data from the dmvs is helpful but on large databases it is not sufficient. My 4TB database takes between 4 and 8 hours to backup. There should be a number of progress messages as the various stages of backup are completed. For example, I have noticed that the DMV will show 100% complete and the Status and Wait Types change but the job still takes an hour or more to actually complete. I think it is probably waiting for the final disk I/O or something similar.
        The option to set a couple of levels of "Verboseness" with messages such as "Calculating Disk Space", "Starting File Group xxx", etc.
        It would also be nice to have some performance diagnostics to help identify the processes or resources that are holding up the backup process.
        It is a real black-box to me and I would like to be able to peek inside sometimes. :)

      Feedback and Knowledge Base