Restore 1.9T on SQL 2008

Question

Restore 1.9T on SQL 2008

Nchax Phoku

Mr or Mrs. 500

Points: 515
More actions
November 16, 2012 at 4:33 am

#284962

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?

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

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

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)

_Beetlejuice Ten Centuries Points: 1064 More actions · Answer 2

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%'

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

_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

Nchax Phoku Mr or Mrs. 500 Points: 515 More actions · Answer 4

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