November 18, 2009 at 11:29 am
Hi All,
My logshipping jobs are running fine; however, the indexes in tables got fragmented and I need to rebuild them sometimes. Every time I do so, it breaks the logshipping. Is there anyway to work around this. Thanks in advance!
Minh
November 18, 2009 at 11:45 am
Would be able to help if error details / description can be posted.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 18, 2009 at 11:54 am
This is a classic issue with logshipping. I presume logshipping breaks because of the size of tranlog backup produced after reindexing?
To avoid going over old ground see this thread
http://www.sqlservercentral.com/Forums/Topic817469-357-1.aspx
---------------------------------------------------------------------
November 18, 2009 at 12:06 pm
Thanks a lot for your help. I got that, too. But it won't help much. My current stupid solution is to kill the logshipping job, rebuild the indexes, shrink the log, and reset up the logshipping. It's a pain,but I don't see of any other way to overcome this at this moment.
Minh
November 18, 2009 at 12:23 pm
so you've tried breaking up the reindex job into chunks, increasing the frequency of the log backups, reorganise instead of reindex?
---------------------------------------------------------------------
November 18, 2009 at 12:49 pm
George has a good point. There are scripts available to help automate the index reorgs rebuilds in smaller chunks at a time.
Smaller reindex operations will help to keep the logshipping running (we had logshipping on a terabyte database that was able to keep running even with the rebuilds due to performing the rebuilds in smaller chunks).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 18, 2009 at 5:45 pm
We had similar problems, although ours was exacerbated because we were using "with standby" at the other end so we had a read-only copy of the database. We ended up turning off our normal log backup job during the reindex job and integrating a log backup task into the reindex job, so that each time an index had completed rebuilding (and if it was longer than 5 minutes since the last log backup) a new log backup would be done. That has the benefit of keeping the log backups around transaction boundaries, so minimising the size that the log will grow to.
As others have pointed out though, shrinking your log after the reindex is finished is a pointless exercise and you need to spend some money for more disk.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply