May 16, 2012 at 7:17 am
Hi All,
I Need an urgent reply on the following issue as we have an Production impact due to the following.
Please Help
Whenever we run rebuilding index for certain tables that has fragmentation more than 30%.
The Log Size of the database increases and Due to which the Logshipping Fails .the copy and restore JObs Starts failing .
Kindly help and Suggest what can be done and What coould be the root cause for the same .
As I have to establish this again all the time to have a Report Servers on Secondary Server.
Please Suggest on the same .In case you need any more details Please let me knw 🙁
May 16, 2012 at 7:25 am
are the primary and secondary servers the same spec? especially in terms of drive sizes?
what error messages do you get on the copy and restore jobs?
it might just be a case that you dont have enough space on the secondary server to hold the larger transaction log backups which in turn causes the failures.
May 16, 2012 at 7:51 am
are the primary and secondary servers the same spec? especially in terms of drive sizes?
Yes The Configuration is Exactly Same .
Do you think that rebuliding index can cause Sync issue on the secondary .
May 16, 2012 at 8:04 am
It can certainly cause sync issues in terms of it has to rebuild the index on the secondary server due to the operation being logged, so if an index rebuild takes 2 hours to complete on server1 it will take 2 hours to complete on server2, if your copy/restored threshold alerts are an hour then you will get out of sync alerts.
Without the errors from the jobs there isnt much I can say, as long as the spec is the same and the secondary has enough space to store the transaction log backups and reply them to the databases, there shouldnt be any issues in rebuilding indexes, it might just be a case of changing your sync thresholds.
May 17, 2012 at 1:36 am
Is it Possible if I Stop the Log Backup ,Copy Back and Restore Backup while I perform the Rebuild indexing Activity .
And Then If
1.I actually run the rebuilding index
2.Take the backup of the log post rebuilding index
3.restore it on secondary Server
4.restart the Three JObs .
Would It Work ....?
Please Suggest.
Thanks in Advance.
May 17, 2012 at 1:42 am
Yes you can do that but you will still have sync issues as your not really changing anything which isnt already setup automatically.
Your still in the situation where if the rebuild takes 2 hours, it takes 2 hours again on the secondary which is no different to what you have now with all jobs running.
The only way to not have sync issues is to not rebuild your indexes or find a better maintenance window, change your existing index rebuild scripts to a better fillfactor. For example, a weekly rebuild which starts on a Friday night, that way you have all weekend to catch up (granted thats only ok if your a Mon-Fri operation)
BUT AGAIN, without the error messages you are getting we cannot advise you on what is actually happening.
May 17, 2012 at 1:45 am
r u Available on CHAT ..
Would like to learn some more from you .
May 17, 2012 at 1:48 am
you can send me a private message on here or keep posting to the forum. I would recommend the forum so that if anyone else has the same issues in the future, they can see the whole picture
May 17, 2012 at 1:50 am
Great thanks Anyways you were really Helpful:-)
I have posted one more issue in case you can help please it would be Great .
http://qa.sqlservercentral.com/Forums/Topic1301523-1550-1.aspx
Thanks Again
May 17, 2012 at 2:14 am
One thing you could try, depending on how often you rebuild your indexes, is to stop the log shipping jobs before the rebuild starts, then restore the whole database on the standby server when reindexing is done, and start your log shipping again at that point. Obviously you'd want to do this during the quietest period.
John
May 17, 2012 at 2:39 am
Y do I need to restore the complete database Y cant I just restore the LOGS backup Post Rebuilding the Indexes.
That would be the same scenerio isnt it.
Atlast you need to have latest logs restored on secondary
1.
I will stop the Backup,Copy and Restore JOB
2.
I will run the rebuilding indexes
3.I will take the backup of logs once done
4.
I will restore the log backup
5.I wll enable the JObs
I think this should work
Please confirm
May 17, 2012 at 2:44 am
What I think John is getting at is which is quicker?
In the example I have used I say 2 hours to perform the index rebuild, so it takes 2 hours to rebuild on the secondary.
If doing a full backup after the rebuild, shipping the backup to the secondary takes less than 2 hours, then that is a better solution to take than shipping the index rebuild logs to the secondary
May 17, 2012 at 2:45 am
If that works for you, then that's the simplest thing to do and you should keep doing it. But rebuilding indexes can cause significant log file activity and so you may find yourself with some very large log backups to copy to the standby server. Depending on how large they are, you may find it easier just to start again.
John
May 17, 2012 at 2:47 am
Thanks both of you ...I just wanted to know the exact reason y do we need to do this .
Would try the same as Advised .
Thanks again .
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply