We are maintaining a stand-by database server by doing a daily manual restore of logs. On weekends we do Reindexing of the tables for performance boost. some of these reindexing jobs take upto 10 Hrs
We have found that the restore of the logs taken in the source server after the reindexing job takes as much time it took to do the reindexing.
Typically 15 transaction log sessions for a day takes 40 minutes to restore. However the restore of the tranlog that was taken after a 10 Hr long reindexing job takes as much time(10 hrs) to do the restore also.
The size of the tranlog does not justify this time frame. ( Size of tran logs after reindex is more than other logs but is less than 3 GB)
I would like to know what exactly is happening during the restore of the tran log. Is it doing the reindexing in the destination system as well?
The destination server (standby) is in non recovered mode all the time.
The interesting thing is that I would only take 8 Hrs to do a full restore of the database. We are using ArcServ software for backup and restore.
We are on SQLServer Enterprise 7.0 sp4 on NT 4 Enterprise on source and destination.
We run SAP R/3 on a MSCS Clustered env.
The source database size is 260 GB
Typically, Reindex or Reorg databases(either thru a maintenance plan or not) will generate large log files. The reason is that SQL would have to record everything in the log and rewrite them back to the .mdf file with the fill factor you specified. When you restore the db using the log file generated by the reorg process, it may take the same amount of time as the reorg. There are a couple ways we tried and saved time. One is to only do the log backup after the reorg so instead of restore from multiple log files you restore from only one. This saves about 15% runtime. The other way was to restore from the complete database backup after the reorg. As you have mentioned, it cut down about 20%.