Restore 1.9T on SQL 2008

  • Hi guys,

    Need urgent help..

    I am restoring 1.9T from multiple backup files, the restore was doing 10% an hour until it got to 80%.

    Its been on 80% for the the last 4 hours, retore I/O are movind and there is activity on the disk.

    What else can I check to verify that the restore is not stuck?

  • Nchax Phoku (11/16/2012)


    Hi guys,

    Need urgent help..

    I am restoring 1.9T from multiple backup files, the restore was doing 10% an hour until it got to 80%.

    Its been on 80% for the the last 4 hours, retore I/O are movind and there is activity on the disk.

    What else can I check to verify that the restore is not stuck?

    Considering the time you asked and the time it is now, the delta time represents the correct answer... just wait. Microsoft's progress reports apparently aren't based on duration.

    As a sidebar, I recommend changing the progress reporting to 1% in the future for this size database.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This query will tell you how long the restore will take in seconds:

    SELECT Percent_Complete, Estimated_Completion_Time / 1000 as [CompletionTimeSeconds]

    FROM sys.dm_exec_requests

    WHERE Command LIKE '%RESTORE%'

  • _Beetlejuice (11/17/2012)


    This query will tell you how long the restore will take in seconds:

    SELECT Percent_Complete, Estimated_Completion_Time / 1000 as [CompletionTimeSeconds]

    FROM sys.dm_exec_requests

    WHERE Command LIKE '%RESTORE%'

    It'll tell you how long the restore is estimated to take, which may be very different from the time it actually does take.

    One reason I can think of, the last portion of a restore is to run crash recovery on the newly restored database, if the log is badly fragmented or there were lots of open transactions at the time of the backup, that recovery process may take a long time. From what I can tell, the progress (STATS = 10) is the percentage of the data copied (which can be easily calculated), not the percentage of time (which is incredibly difficult to estimate correctly)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks everyone for responding.

    @jeff, I think I should do that next time

    @_Beetlejuice, i got hold of the script but it gave me different estimated_completion_time

    each time I ran it.

    @gilamonster, the log file was only 30G - not sure about the open transactions though.

    By the way, the Restore took 8 hours from 80% to 100%.

    Kind Regards

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply