We are a medical facility and we will need to recover STAT
We are trying to restore our SQL 2000 database using the comand below
RESTORE DATABASE PowerPath FROM DISK = 'F:\MSSQL\BACKUP\DATABASENAME.BAK' WITH NORECOVERY , STATS = 10 ;
After 22 hours we recieved a message that its 10 percent done.
The database is large. 1.5 TB and normally it take over 6 hours just to back up.
We have done some research and we think this may be the problem of why this is taking so long:
This can happen if the log file has grown several times (usually as a result of very small auto-growth setting). As a result, the log will have several 1000 to several million VLFs (you can read more about VLFs in Transaction Log Physical Architecture). The first phase of recovering a database is called discovery where all the VLFs are scanned (in serial and single threaded fashion) before actual recovery starts. Since this happens much before the analysis phase, there are no messages indicating the progress in the SQL Server error log. Depending on the number of VLFs this initial discovery phase can take several hours even if there are no transactions in the log that need to be processed.
My questions are:
Has anyone had a restore take this long?
Is there a way for us to tell if anything is happening?
Can we believe the counter that says its 10 % complete?
Do we let the restore continue to run?
Are there any alternatives?