May 30, 2008 at 9:00 am
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
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.
May 30, 2008 at 11:53 am
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
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
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