October 19, 2005 at 9:18 am
As part of a move of an applicatoin from the US to a data center in Europe we plan on keeping the current production servers as the DR servers. Due to network capacity, the plan is now leaning towards doing a full database restore on the DR servers monthly during low network activity and just transfer and restore the transaction logs every day in between. Currently we have a job setup to defrag and/or rebuild indexes on the databases every Sunday. On those days the combined transaction log backups are about the same size as the full database backups. Given that, I don't know if we should restore the full database backups weekly on the DR server(may not be able to because of network traffice) or change the job to defrag/rebuild indexes monthly. How often should fragemented indexes be rebuilt on an active OLTP database? Is monthly good enough?
October 19, 2005 at 9:41 am
Since your mentioned your log shipping, you have to understand that reindexing will inflate the size of your logs forcing you to ship more data over the wire when the log ships.
There is no set time on when to reindex. You should evaluate each and every table. Define what actions are performed on each table, Static tables where little or no data is inserted can be left out. But tables that have alot of inserts, and alot of reads should be indexed as often as you need to maintain the level of performance you want.
Tables that alot of inserts and no reads do not need reindexing very often. Bottom line only reindex when the health of the index is below acceptable levels. But the monthly shotgun approach is good too, if you don't care about the size of the logs.
October 19, 2005 at 10:04 am
Thanks for your response Ray. Believe me I understand that reindexing causes the log backups to grow. That is why I am thinking about changing it from weekly to monthly to coincide with the full database restore on the DR server. That way when the full database restore is done we will transfer the database backups for that day instead of the log backups and make sure it's the same day we do the reindexing before the database backup if that makes sense. We currently use a stored procedure I found at this site to do the defrag and then rebuild indexes if needed. We run that weekly, but I'm thinking we should change it to monthly and see what kind of performance impact it has. Unfortunately, we're under short time constraints, so I don't have alot of time to do analysis.
October 20, 2005 at 7:30 am
We are using Log Shipping and because the transaction logs grew so large, I ended up splitting up the tables to be reindexed. It took a while to figure out when to reindex what, but at least the restore of the log files are more manageable now. I started with scheduling the largest tables separately on different days, and added the rest on another day.
Linda
October 20, 2005 at 7:39 am
Linda,
That sounds like a good idea. I will look into that, I'm just concerned about the locking issue with the reindexing, I'm not sure how much we can spread it out. Since you are familiar with log shipping, I have another question for you. To restore the primary database in a log shipping environment, do you need to remove or disable the log shipping, restore and then reenable?
Thanks,
Michelle
October 20, 2005 at 8:10 am
I've never had to restore the primary database yet, but I believe you would need to remove log shipping, restore, and set up from scratch. The reason is that the LSN numbers that Log Shipping uses for the Transaction Log restores will no longer be sequential (FirstLsn and LastLsn). Run this on one of your log files to see what I mean. Hope this helps.
RESTORE HEADERONLY FROM DISK = 'path\log_file_name.TRN'
Linda
October 20, 2005 at 8:14 am
Thanks, I guess my assumptions were right. I will remove the log shipping, restore and then recreate the log shipping. Thanks for the help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply