July 29, 2014 at 3:57 am
Hello,
We have been getting unexpectedly long restore times against a couple of databases and was hoping someone could point me in the right direction! All databases have daily FULL backups and 15MIN log backups on LIVE and are restored to REPORTS.
These databases get log restores (manual NOT logshipping) every hour for the last 4 x 15MIN log backups.
They are all controlled in job steps so do not overlap.
The rough restore job goes like this:
- SET single-user
- RESTORE LOG with NORECOVERY (00:15)
- RESTORE LOG with NORECOVERY (00:30)
- RESTORE LOG with NORECOVERY (00:45)
- RESTORE LOG with NORECOVERY (01:00)
- RESTORE DATABASE with STANDBY
- SET multi-user
The databases in question are:
- AUDIT - 5gb MDF - 1gb LDF - 30kb -> 3mb TRN
- MGMT - 4gb MDF - 1gb LDF - ~80kb TRN
The VLF counts are:
- AUDIT - 239
- MGMT - 278
I turned on flags 3004 and 3605 and scanned the restore logs. Compared to our 150GB database ranging from 5MB -> 100MB log backups the restore times on the Restore-Redo for that are under 20 seconds on average....the 2 databases in question average around 1m30s per restore file....which is adding at least 6 minutes to each restore job. Then the "Starting-Up Database" step until "Database is in STANDBY" takes around 2 minutes for the databases in question whereas other much busier, larger databases take under 15 seconds.
I know size is not a determining factor but am assuming activity is due to the restore-redo....but at times when there is little to no activity on the databases they seem to take the longest.
There are some heavily fragmented indexes but unsure how these would affect log restores. The standby files are all small (80kb) and all files are held on a NAS with no perceivable performance issues there....moving over to the NAS cut 40% of the restore time.
Any ideas what i can check or query to see if something is different in the structure, fragmentation, or other reason for this??
Many Thanks Advance!
Samuel
July 29, 2014 at 4:22 am
a couple of specific questions:
- is there any way to see how many transactions have to be rolled back/forward during the restore?
- any way to check the startup procedure to see where the time is being used?
- i noticed that the time taken for recovery is less than the time shown in the log:
ie:
10:43:01 - starting up database
10:43:44 - recovery writing checkpoint
10:43:47 - recovery complete in 25 seconds (analysis 8367ms, redo 0ms, undo 9508ms)
this seems inaccurate and the actual log restores only takes 0.05 seconds at most!!
thanks again
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply