Slow restore
We are finding transaction log restores using SQLBackup 4.6.0.815 taken an extraordinary length of time, despite the message claiming it only took a split second.
The restore command returned the response:
SQL Backup v4.6.0.815
Restoring xxx (transaction logs) from:
H:\xxx\xxx\xxx.sqb
Processed 12 pages for database xxx, file xxx_log on file 1.
RESTORE LOG successfully processed 12 pages in 0.039 seconds (2.415 MB/sec).
Yet in profiler I see
RESTORE LOG [xxx] FROM VIRTUAL_DEVICE = 'SQLBACKUP_5A2DF637-52D6-40C3-8441-546831C10D3C' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576
with a duration of 47466 ms
Can you explain why this discrepancy? What else is likely to cause the additional 47.5 seconds after (or before) the restore is actually carried out?
We currently have just 1 month of backup/restore history held in msdb, which is under 100Mb
Thanks
Tim
The restore command returned the response:
SQL Backup v4.6.0.815
Restoring xxx (transaction logs) from:
H:\xxx\xxx\xxx.sqb
Processed 12 pages for database xxx, file xxx_log on file 1.
RESTORE LOG successfully processed 12 pages in 0.039 seconds (2.415 MB/sec).
Yet in profiler I see
RESTORE LOG [xxx] FROM VIRTUAL_DEVICE = 'SQLBACKUP_5A2DF637-52D6-40C3-8441-546831C10D3C' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576
with a duration of 47466 ms
Can you explain why this discrepancy? What else is likely to cause the additional 47.5 seconds after (or before) the restore is actually carried out?
We currently have just 1 month of backup/restore history held in msdb, which is under 100Mb
Thanks
Tim
Comments
If you are using the ERASEFILES option, try omitting it and see if the restore process completes faster.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8