1

I have 7 databases on my reporting server. I restore those databases every night with differetial backups. Normally, the differential restore time was taking about 1 or 1.5 hour for each databases. Last day, my restore process took so much long time. 4 of those databases has restored about 11 hours. The other one was completed normally. What can cause those databases to take such a long restoring time?

1

As long as you state that you did restore before and it took much less time I would quess that some of the following could be the reason:

  • some other process on the reporting server taking a lot of resources (in- and outside of SQL Server), that put high load on disks;
  • slow network connection between backups and reporting server if backups are located on a network share;
  • some process loading the disks with backups preventing fast reading of them by SQL Server.
1
  1. The Log file of database is fragmented too much and there are too many Virtual Log Files. This would force recovery to take longer time. Run dbcc loginfo(db_name) on database and tell me how many rows it returns. Please see How a Log file Can affect database restore time

  2. The SQL Server service account does not have "Perform Volume Maintenance Task" privilege or what you call as Instant File Initialization is enabled or not. If not the restore time can significantly increase

  3. I suggest you have a look at Optimizing Backup and Restore Performance in SQL Server

  • 1
    First Db has 2654 rows. 2. has 1870 rows. 3. has 312 rows. 4. has 35715 rows. 5 has 76 rows. Instant File Initialization is enabled. I checked it once again. @benedikt – Dbo Sep 12 at 13:33
  • reduce the number of VLs using the steps from blogs.msdn.microsoft.com/psssql/2009/05/21/… – Benedikt Sep 12 at 13:45
  • 1
    Thank you very much for your answers. – Dbo Sep 12 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.