December 20, 2010 at 2:29 pm
The issues (bloated tlog) I used to experienced were on sql2005 sp1 and sql2008 rtm.
I used structured dynamic codes something like 'use dbname; alter index .. rebuild online'
Thank you David and Gail for the very useful info you provided.
December 20, 2010 at 2:32 pm
I wonder how to explain the outofsync and bloated tlog in publication db due to the pending replication.
Is your Publication database also the Principal in a Mirroring session?
Were this the case, your index optimization activity could be increasing the time it takes transactions to be passed to the mirror, which must be applied in the correct order. This would cause a delay for transactions hardening on the Mirror and therefore also a delay in transactions at the Principal/Publisher being marked for Replication.
December 20, 2010 at 2:34 pm
No. The very large db was not engaged in mirroring at all.
December 20, 2010 at 2:37 pm
Pei Zhu-415513 (12/20/2010)
The issues (bloated tlog) I used to experienced were on sql2005 sp1 and sql2008 rtm.I used structured dynamic codes something like 'use dbname; alter index .. rebuild online'
Thank you David and Gail for the very useful info you provided.
I thought when you were saying "out of sync" that you meant you were missing transactions. If you were referring to increased latency then yes, as Gail stated, the bloated log will cause that all day long. If you have a large database with large index rebuild operations you either have to expect that or build around that. We actually watch log size during our operations and stop when we get to a certain point, move to another database and then come back after doing some other intervention to get the log size down. May seem like overkill but if you have high uptime requirements and replication plays a part in that you have to make it so that it can continue to perform efficiently. Keeping the transaction log size managed is critical in that regard.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 20, 2010 at 2:53 pm
David, Yes I meant out of sync (missing transaction). Also Tlog was bloated and continued to grow until I broke the replication or ran repldone.
I wonder if you could show me your pieces of codes how to stop and go when rebuilding index automatically.
My guess would be before rebuilding a single index you check log size/free space.
December 20, 2010 at 2:58 pm
Pei Zhu-415513 (12/20/2010)
David, Yes I meant out of sync (missing transaction). Also Tlog was bloated and continued to grow until I broke the replication or ran repldone.I wonder if you could show me your pieces of codes how to stop and go when rebuilding index automatically.
My guess would be before rebuilding a single index you check log size/free space.
The missing transaction might be covered by the bug that I listed in my earlier post. Read that and let me know if your current version fits that scenario.
Your assumption on how we handle that is correct. Can't really go much beyond that presently though.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 20, 2010 at 3:04 pm
The missing transaction might be covered by the bug that I listed in my earlier post. Read that and let me know if your current version fits that scenario.
Yes. In my previous post. They were on enterprise sql 2005 sp1 and enterprise sql 2008 rtm.
December 20, 2010 at 3:06 pm
Ok - sorry about that. You should definitely look at getting the latest service packs in place then to solve that issue.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 20, 2010 at 3:16 pm
The discussions lead me to conclude that turn the db to bulk logged before rebuilding indexes on large db and put it back to full recovery is good practice if you could afford a much larger tlog backup afterwards.
Could anyone comment on it?
December 20, 2010 at 3:49 pm
That is a recommended practice and the large transaction log backup post index rebuilds should be expected. I will say though that you will still see some log bloat even in recovery models other than full so still take that into consideration.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 20, 2010 at 10:06 pm
Hi,
thank u a lot.. 🙂
Regards,
December 20, 2010 at 11:48 pm
Pei Zhu-415513 (12/20/2010)
The discussions lead me to conclude that turn the db to bulk logged before rebuilding indexes on large db and put it back to full recovery is good practice if you could afford a much larger tlog backup afterwards.
And if you can afford the slight risk of not been able to restore to point-in-time during the period you're in bulk, just to time of a tran log backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply