July 5, 2012 at 8:43 am
Right now we backup transaction log every 15 minutes, every day of the week.
During index maintenance on saturday evening, the dimension of the transaction log increase very fast and can happen that in just 15 minutes this file reach the end of the disk.
I would like to create and use another job, only for the day of the index maintenance, to backup the transaction log every 1 minute, maybe less.
In my understanding this should prevent the log to explode.
Dear experts, could you please give me a feedback?
Thank you very much.
Best Regards.
Frank
Franco
July 5, 2012 at 9:18 am
are you doing index rebuilds or index reorganisation? it sounds like a rebuild
if it is a rebuild then changing the t-log backup schedule would be pointless as the entire index rebuild statement will remain active in the log until it is completed. running a t-log backup will not truncate the log until the operation has completed.
if this is an issue then perhaps consider switching to index reorganisation until you can find some space on your server to add new log files
MVDBA
July 5, 2012 at 9:34 am
maybe try a rebuild using the SORT_IN_TEMPDB option ?
this should prevent the transaction log from ballooning.. just make sure you have enough space in your tempdb to accommodate the amount of index data it will store.
cheers
vultar
July 5, 2012 at 9:42 am
vultar (7/5/2012)
maybe try a rebuild using the SORT_IN_TEMPDB option ?this should prevent the transaction log from ballooning.. just make sure you have enough space in your tempdb to accommodate the amount of index data it will store.
cheers
vultar
on second thoughts, this option will only stop the data file from growing so I'd give my suggestion a miss!
cheers
vultar
July 5, 2012 at 9:45 am
Or, if you can afford to lose point in time recovery during the index maintenance period, have the the process change the database from FULL recovery model to BULK_LOGGED recovery model before the rebuild process starts, and change it back when completed. This will keep the t-log from ballooning, just remebr that the t-log backup will not be small.
July 5, 2012 at 12:51 pm
If you can break your reindexing into separate transactions (separate tables), then running a log backup in the middle can help.
Or use a script that reindexes only some tables when they need it.
July 6, 2012 at 12:35 am
Thank you very much for all your valuable advice, I really appreciated.
The index maintenance procedure is a mix of rebuild and reorganization, its really depends of the results of the avg_fragmentation_in_percent give by
sys.dm_db_index_physical_stats.
So some times the index is only reorganized but it can happen that it will be rebuild, typically when fragmentation is greater than 30 percent.
Changing the recovery model to BULK_LOGGED before the rebuild process starts can really be a good option.
I'll do some test in out dedicated test box.
Best Regards.
Franco
July 10, 2012 at 1:01 am
I have done my tests and I can say that the log do increase rapidly even in BULK-LOGGED recovery model.:exclamation:
Due to the fact that our users stop to work at 6 PM on friday and the index maintenance is scheduled for saturady 7 PM, I was thinking of changing the recovery model to SIMPLE following this criteria:
1 -Full db backup
2 -alter db to simple recovery model
3 -index maintenance
4 -alter db to full recovery model
5 -full db backup
Dear experts, could you please give me a feedback?
Thank you very much.
Best Regards.
Franco
July 10, 2012 at 3:35 am
franco (7/10/2012)
I have done my tests and I can say that the log do increase rapidly even in BULK-LOGGED recovery model.:exclamation:Due to the fact that our users stop to work at 6 PM on friday and the index maintenance is scheduled for saturady 7 PM, I was thinking of changing the recovery model to SIMPLE following this criteria:
1 -Full db backup
2 -alter db to simple recovery model
3 -index maintenance
4 -alter db to full recovery model
5 -full db backup
Dear experts, could you please give me a feedback?
Thank you very much.
Best Regards.
Changing the recovery model to simple is not recommended for the fact it will break the chain of the log in case if you are having log shipping or database mirroring.
If you rebuild index occupies more space means that you have to look in details of the hardware details. The fact is you are not having enough disk space.
Break down the whole index rebuild to smaller part and run in different schedules. There are many scripts in the internet to do index rebuild.
I use one from SQLwild. Please check the scripts it might be useful to your scenario.
July 10, 2012 at 3:55 am
Changing the recovery model to simple is not recommended for the fact it will break the chain of the log in case if you are having log shipping or database mirroring.
If you rebuild index occupies more space means that you have to look in details of the hardware details. The fact is you are not having enough disk space.
Break down the whole index rebuild to smaller part and run in different schedules. There are many scripts in the internet to do index rebuild.
I use one from SQLwild. Please check the scripts it might be useful to your scenario.
i wouldn't say "not recommended!" - as long as you aren't running mirroring or log shipping (and after you switch back to full recovery mode you take a full backup) then this should be fine.
such sweeping statements often get turned into urban myth that "simple mode is not recommended" - evaluate the risk of losing data that is updated while you are performing your index rebuilds and what the impact to the business would be if you had to restore to the point before the index rebuild. then you can make a decision
MVDBA
July 10, 2012 at 4:01 am
Thank you Michael, in fact the risk is very low due to the fact that I'll take a full db backup before index maintenance.
Franco
July 10, 2012 at 8:34 am
franco (7/10/2012)
I have done my tests and I can say that the log do increase rapidly even in BULK-LOGGED recovery model.:exclamation:Due to the fact that our users stop to work at 6 PM on friday and the index maintenance is scheduled for saturady 7 PM, I was thinking of changing the recovery model to SIMPLE following this criteria:
1 -Full db backup
2 -alter db to simple recovery model
3 -index maintenance
4 -alter db to full recovery model
5 -full db backup
Dear experts, could you please give me a feedback?
Thank you very much.
Best Regards.
If you can test this in a test environment, I would. There is a very good possibility that you will have the same problem switching to the simple recovery model as you do with the bulk_logged recovery model.
As others have said, you may need to break down the index maintenance process into more discrete parts.
July 10, 2012 at 8:41 am
As long as you aren't causing problems downstream, as noted above, switching to simple is fine. There are people that do this for large data loads as well.
You might look at the index maintenance script from @SQLFool - http://sqlfool.com/2010/04/index-defrag-script-v4-0/
July 11, 2012 at 1:29 am
Good Morning, thank you very much for all your great suggestions.
Best Regards.
Franco
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply