August 16, 2013 at 6:48 am
Hi
We have a nightly maintenance task that reorgansies the indexes and checks db integrity across all our servers.
The job was failing upto 2 days ago on one particular server beacuse of a table that had an index that didn't allow page level locks. I changed the index to allow the locks and now the job runs. However, i have noticed since then, that the database log file that the table belongs to auto grows during the night. I have checked this by quering the msdb database for log file auto events and i can see it grows by apporx 5Gb - not leaving us much space on our drive. The actual log file is now 10Gb and during the day we back it up every 10mins so it never really gets anywhere near 10Gb - this only happens at night when the maintanence job runs.
So, am i correct in assuming it is the index maintanence job that causes the growth ? And will it always need this 10Gb of space to complete the task ?
August 16, 2013 at 7:53 am
It does; presumably so the indexing can be rolled back if necessary.
Do you definitely need to re-index every night? Are you using a maintenance plan, is that why it's a nightly task- or are you identifying just the ones that need it?
August 16, 2013 at 8:05 am
Reoraginzing \ Rebuildind indexs will generate a large amount of Transaction log activity. You can reduce the effect by changing the recovery model pre maintenance to Bulk-Logged or simple to reduce the amount of log generated.
There is consequences of making this change in terms of backups and the ability to recover from a disaster than you would need to take into consideration.
MCITP SQL 2005, MCSA SQL 2012
August 16, 2013 at 8:06 am
Index rebuilds in full recovery model are a 'size of data' operation. The entire index, every single modified page, gets logged, so the log space required is > size of index.
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 16, 2013 at 8:29 am
You could create a job to change your recovery model during the process then switch it back to full mode after and fire off a full back up.
August 16, 2013 at 8:43 am
gasbod (8/16/2013)
You could create a job to change your recovery model during the process then switch it back to full mode after and fire off a full back up.
If you mean switch to simple, that's generally a very bad idea. If you mean switch to bulk-logged, then a full backup is not necessary though a log backup is recommended.
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 16, 2013 at 8:50 am
Yeah very true, I'd first try Bulk logged.
But if the only process going on in the re-index I see no harm in switching out into Simple mode, apart from breaking your Transaction log backup chain, which would be fixed by the full backup at the end. if the re-index should fail at that point or some other failure would you really want to be restoring beyond the point of starting the re-index?
August 16, 2013 at 9:23 am
And if you go to restore and that full backup taken after the switch back to full is damaged and won't restore? Or lost in a drive failure?
Intentionally breaking the log chain means you have no alternatives, no possible other restore paths than one starting from the full after the reindex. Not a good position to be in and I've seen cases where exactly this setup results in massive data loss in a disaster.
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 16, 2013 at 9:44 am
But wouldn't you be able to just restore from the full backup (say from the night before) and then the Tran Logs up until you started the process...
or if it all worked fine then you have your Full backup to go to for anything after the event..
anyway its kind of a moot point, switching the bulk logged should do the trick anyway... other alternative could be to do the re-index in small batches to allow the log to grow at an acceptable pace then the shrink after completion..
August 16, 2013 at 10:05 am
I think my main concern here is that the file has grown to approx 10Gb and we i have 3Gb free on my drive.
During the day the log never grows anywhere near 10Gb. If at the end of the day my lof file is only consuming say 1Gb of the grown 10Gb log file, then when the index maintanence job kicks in, will it just consume the remaining 9Gb and not grow any more ? If it does this, then that should be ok - if its going to get any bigger then i havent much disc spare for it to grow !
August 16, 2013 at 10:21 am
You never answered
Beatrix Kiddo (8/16/2013)
Do you definitely need to re-index every night? Are you using a maintenance plan, is that why it's a nightly task- or are you identifying just the ones that need it?
You definitely don't want to shrink the log. It will just grow back to this size or larger. So you need to either reduce the usage by doing something about your maintenance or you need more drive space.
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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply