February 8, 2012 at 1:51 pm
SQL Server 2000 8.00.818 SP3 Enterprise Edition
We have a 305GB database that we are trying to restore from a backup file located on a disk local to the server. This process had worked as of last may but the database size would have been under 300GB.
Here is the restore script in it's latest version -
USE master
GO
RESTORE DATABASE [JDE_CRP] FROM DISK = N'I:\MSSQLTest\Backup\REFRESH_DEVICE.BAK' WITH FILE = 1
, MOVE N'JDE_PRODUCTION_Data' TO N'I:\MSSQLTest\JDE_CRP_Data.MDF'
, MOVE N'JDE_PRODUCTION_log' TO N'I:\MSSQLTest\JDE_CRP_Log.LDF'
, NOUNLOAD, REPLACE, RECOVERY, STATS = 5
GO
The data and log files are being created in the target directory and are the exact same size as the files for the source database the backup was taken from are so it looks like all should be fine however, the process never finishes and does not report stats. SSMS shows the database (Refreshing...) and no stats are being reported.
February 8, 2012 at 2:02 pm
First step in that restore would be zeroing out the files. While the files are getting zeroed out you won't see any stats because the restore itself hasn't started, it's just the file creation process (and the files appear to be at full size all through that process)
Not sure how to monitor a backup in SQL 2000, and the 2008 stuff I posted wouldn't have helped.
Are you seeing disk contention? High disk latencies?
Edit: corrected for SQL 2000. Please post SQL 2000 questions in the SQL 2000 forums
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
February 8, 2012 at 2:29 pm
Looks like the disk utilization is very high on the DB server so that may be a big part of the problem. I am going to let the current restore run through the night and see if I ever get stats back. We will try again after the next server reboot if this does not work.
Thank you for the feedback.
February 8, 2012 at 2:32 pm
Seeing any interesting messages in the errorlog?
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
February 8, 2012 at 2:39 pm
I assume you are referring to the SQL Server current log. It's pretty clean. I see where we had our daily backups done but nothing else.
Checking the server log we don't see anything either.
February 8, 2012 at 3:18 pm
3 hours into it I got my first stat of 5% back. There is obviously sonething very wrong but at least I now know it's actually trying to do something.
Thank you for the feedback. It has helped to keep me from going down another blind alley.
February 8, 2012 at 3:25 pm
My guess - very slow IO subsystem (for whatever reason). Both files have to be zeroed out entirely and that can take time. Shouldn't take 3 hours for a 300GB DB however....
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply