Slow restores | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Slow restoresHere is the situation. I have a SQL Server 2000 Production Database running on a Windows 2003 Cluster. Every night we backup the database to another server which has the identical configuration except that it is not in a cluster. We then restore the database to use as a reporting copy. The database is about 300GB and we use Litespeed as our backup/restore tool. Until recently the backup would take about 30 minutes and the restore was taking around 30 minutes. About a week ago the restore suddenly began taking over 2 hours to run. Since this reporting copy is a source of our Data Warehouse ETL processes, we have a very tight window for getting the restore done. More data:
This increased restore time only occurs during its normally scheduled window which is a start time of the backup at midnight. If I run the entire process at another time, say 10PM, the restore again executes in around 30 minutes. The only process running at the postmidnight restore is the restore process. I have confirmed this by checking sysprocesses and the only connections are from the restore. There are no other server processes (at least none that I can detect) running during the slow restore. I even ran SQLioSIM during the slow restore, using test files in the same location as the target database and got excellent IO durations. Any thoughts, suggestions, ideas? Anything will help. I can’t continue staying up until 3AM every night babysitting this process.
Welcome to the forums.
A background on the problem that certainly gives a good explanation on your monitoring.
Do you see any additional jobs are scheduled during the time of restore (say anti-virus checks on Windows)?
Are you using log shipping to ship the transaction logs between the servers?
Also what is the service pack level on SQL Server?
Actually, the problem was tracked back to another job on another sql server that was causing extremely high IO in the SAN. Disabling the job solved the problem.