Post reply

Where In The Restore Progress Am I?

  • May 30, 2008 at 9:00 am

    #207840

    Hello, we started a restore of a database that is just over 1.2 TB. It took 23 hours to complete the Copy Data Phase. The sys.dm_exec_requests shows Percent_Complete is 100, however, we know that its not done, the Data Copy Phase has completed but the Severe is still churning. We did not set the NORECOVERY flag and we did not use the WITH STATS = switch. This restore started on 5/28 at 8:30 am and its now 5/30 at 10:30 am with the database showing that its state is "In Recovery" and with no way of determining an ETA for it coming online. Its there a command, script, or other tool that we can use to determine where in the Restore process we are and how much time may be remaining?

    Thanks, Clay

  • May 30, 2008 at 9:08 am

    #822396

    Not sure, but I'll ask around. You can ask for %complete in this command using the stats option. Not sure that this is what dm_exec shows.

    Have you checked the log for roll forward / roll back messages?

    There is a restart option, so if you stopped the service or rebooted the machine, you might be able to restart things where they stopped.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • May 30, 2008 at 11:53 am

    #822484

    Use the following query to estimate the restore time...

    selecT percent_complete ,((estimated_Completion_time/1000.0)/60)/60 as Estimated_timeinHRS

    from sys.dm_exec_requests where session_id=

    I don't think RESTART command is any useful unless you are restoring from TAPE.

    MohammedU
    Microsoft SQL Server MVP

  • May 30, 2008 at 12:08 pm

    #822489

    Use this all the time....

    SET NOCOUNT ON

    GO

    SELECT Command

    , 'EstimatedEndTime' = CONVERT(varchar(26),Dateadd(ms,estimated_completion_time,Getdate()),100)

    , 'EstimatedSecondsToEnd' = CONVERT(decimal(9,2),(estimated_completion_time * .001))

    , 'EstimatedMinutesToEnd' = CONVERT(decimal(9,2),(estimated_completion_time * .001 / 60))

    , 'OperationStartTime' = CONVERT(varchar(26),start_time,100)

    , 'PercentComplete' = percent_complete

    FROM sys.dm_exec_requests

    WHERE command IN ('BACKUP DATABASE','RESTORE DATABASE')

    GO

    Your friendly High-Tech Janitor... 🙂

  • May 30, 2008 at 2:10 pm

    #822546

    My bet is there's a ton of transaction log being recovered. Can you check the disk IOs on the log drive?

    Beware of trusting the estimated completion times in the dm_exec_requests DMV - they're not very accurate and can vary wildly depending on a huge number of variables.

    Thanks

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

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

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