August 5, 2011 at 5:55 am
We've SQL Server 2005 Standard installed.
I've run a script to manage the defragmentation of the indexes (http://sqlfool.com/2011/06/index-defrag-script-v4-1).
Now we have a big index to rebuild (page count = 6,6milj) and when running this rebuild (duration already over the 3 hours) our transaction log for the database went full and we received errors/problems.
We'll take every 30minutes a transaction log backup.
The rebuild index started at 1:00 end ended with error on 4:15
But in meanwhile there were succesfull log backups...
Does a rebuild index keeps its transactions in the log till it's ended (commited) so the log can't be emptied (free space created)?
How can I solve this problem with this (big) index?
August 5, 2011 at 6:00 am
One thing I would do is allow the transaction log to grow.
August 5, 2011 at 6:02 am
It could grow till disk is full (=40Gb).
That was the problem.. 🙁
August 5, 2011 at 6:06 am
Yes if could, in which case you need more disk space.
To rebuild an index in full recovery model you need as much log space as the total size of the index, at least.
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
August 5, 2011 at 6:07 am
Is there another drive with more space that you could move the log to?
August 5, 2011 at 6:09 am
Rhox (8/5/2011)
Does a rebuild index keeps its transactions in the log till it's ended (commited) so the log can't be emptied (free space created)?
Yes. Index rebuilds are done as a single transaction, hence the log from the point the transaction started until it commits or rolls back will remain active.
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
August 5, 2011 at 6:09 am
No diskspace free at the moment ... 🙁
And the database can't have a downtime 🙁
August 5, 2011 at 6:13 am
You are up against a wall on this. You will need more storage to accomplish the task. Which will require an outage to move the log to.
also: No downtime at all? Never? Not even Sunday at 3 AM?
August 5, 2011 at 6:14 am
I'm not too sure what to tell you about this, but this might help.
Here I do tlog backups every 15 minutes and the reindex job used to run well over that untill I updated it. I checked the log history and at the 15 minute mark, during the reindex job, I have a very big tlog backup taken. So I don't think this is the issue here.
6.6M pages = ±50GB. So any way you put it 40 GB is not enough room to run this (especially if you take the backups and put them on the same drive as the logs themselves... which is a big no-no anyways). BTW a conservative estimate for a rebuild would be 1.25 to 1.5X so assume 75 GB to rebuild.
Here are things I would try to see if it fixes it.
That script can rebuild partitions IIRC. So maybe try manually doing 1 at a time and then only doing the last one (assuming partitioned on date or anything that makes only the last partition being insert/ heavy).
Get bigger drives. (I know, doh!)
Try putting that table in the ignore list and then manually start a reorg 10-20 minutes before the next tlog backup. I don't know this 100 for sure but since this is online operation it might be logged more frequently (meaning you can take a tlog and mark the log as reusable).
P.S. A fast, even only decent, san can probably fill 40GB in less than 10 minutes so you need to know what your san can do to get the correct timing.
Extra pointers you might like to know about : http://www.sqlservercentral.com/Forums/Topic1126147-146-1.aspx
August 5, 2011 at 6:15 am
GilaMonster (8/5/2011)
Yes if could, in which case you need more disk space.To rebuild an index in full recovery model you need as much log space as the total size of the index, at least.
I was afraid of this answer 🙁
Thanks for the info, I did not found it quickly on Google... I love this community!
August 5, 2011 at 6:20 am
crashdan (8/5/2011)
You are up against a wall on this. You will need more storage to accomplish the task. Which will require an outage to move the log to.also: No downtime at all? Never? Not even Sunday at 3 AM?
They don't want a downtime, it's an important news database...
And I know that more diskspace will solve the problem, but that's a no go at this moment 🙁
August 5, 2011 at 6:22 am
ouch. Performance won't be the best, but do what you can with the other indexes and keeping stats up to date.
August 5, 2011 at 6:24 am
Ninja's_RGR'us (8/5/2011)
Here I do tlog backups every 15 minutes and the reindex job used to run well over that untill I updated it. I checked the log history and at the 15 minute mark, during the reindex job, I have a very big tlog backup taken. So I don't think this is the issue here.
Yes, you will, because the used portion of the log is large. However that log backup will not mark the log as reusable because there is an open transaction. The log can't be marked reusable past the beginning of the oldest open transaction.
6.6M pages = ±50GB. So any way you put it 40 GB is not enough room to run this (especially if you take the backups and put them on the same drive as the logs themselves... which is a big no-no anyways). BTW a conservative estimate for a rebuild would be 1.25 to 1.5X so assume 75 GB to rebuild.
I'd say 100GB, the log has to reserve space for rollback as well, and that can be the same size as the roll-forward space usage.
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
August 5, 2011 at 6:24 am
Thanks for the support.
I will exlude this index (by using the @maxPageCount parameter) at this moment, so the other indexes can be maintenanced.
And for this big one, I will discuss it with the managers how to solve it (diskspace...).
I will manualy change the recovery model to simpel and manualy rebuild the index on the best time (of the week).
August 5, 2011 at 6:25 am
Rhox (8/5/2011)
crashdan (8/5/2011)
You are up against a wall on this. You will need more storage to accomplish the task. Which will require an outage to move the log to.also: No downtime at all? Never? Not even Sunday at 3 AM?
They don't want a downtime, it's an important news database...
And I know that more diskspace will solve the problem, but that's a no go at this moment 🙁
What's your data loss allowance?
You can switch to bulk-logged recovery for the duration of the rebuild. It'll reduce the log space used, but the risk is if there's a failure of any form you won't be able to take a tail-log backup and could end up losing data. If that's acceptable, switch to bulk-logged, rebuild the indexes, switch back to full, take a 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 15 posts - 1 through 15 (of 58 total)
You must be logged in to reply to this topic. Login to reply