How minimice the transaction log file growth during maintenance plain

Question

How minimice the transaction log file growth during maintenance plain

ico-601891

Hall of Fame

Points: 3992
More actions
February 22, 2016 at 10:14 am

#305016

Hello,
I have a database with the recovery mode defined as FULL. I have defined a index maintenance plain to run every weekend. During the week many of the indexes are very fragmented so they are rebuilded at the weekend. In this way the transaction log file size grows so much. I can't change de recovery mode during the maintenance plain process, and I can't do transaction log file backup with more frecuency.
¿Is there any other way to minimize the growth of transaction log file during maintenance plain? (I also tested to create the indexes with SORT_IN_TEMPDB option but the results are the same -the transaction log file size is very big-).
Many thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

a maintenance plan tends to use a shot gun approach: rebuild all indexes, regardless of whether they need to be rebuilt or not.

you are probably at that tipping point where a more detailed, refined process would be better for you.

Two peer reviewed scripts that are out there are http://www.midnightsql.com/minion/minion-reindex/ and https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html, both of which give you a fire-and-forget approach to reindexing, and use logic to determine whether an index needs to be rebuilt or simply reorganized, or skipped because there's no significant reason.

I would suggest you take a look at both, and replace your maintenance plan with one of them instead.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ico-601891 Hall of Fame Points: 3992 More actions · Answer 2

Hello Lowell.

Thanks you for your answer. We are already using 'ola.hallengren' solution for maintenance plain. ¿Do you have any other suggestion?

Thanks in advance.

MadAdmin SSChampion Points: 11261 More actions · Answer 3

Optimise your indexing. Tlog is used to rebuild unused indexes as well so get rid of the dead weight. Also I set threshols higher and run online daily so that I only rebiild max 1 or 2 a day. And hence small daily hit to tlog.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

ico-601891 Hall of Fame Points: 3992 More actions · Answer 4

ico-601891

Hall of Fame

Points: 3992

February 23, 2016 at 2:10 am

#1860306

Thanks you for your help.

Jon.Morisi SSChampion Points: 12846 More actions · Answer 5

Just brainstorming here...

Based on your limitations you could try to get creative with the timelimit and delay parameters of the OLA script with the intent of slowing down your index maintenance so that LOG backups could occur between index maintenance executions. Check the OLA log table to see if it would make sense (hopefully you have logging enabled). If you have one particular INDEX that's taking 100x longer than the rest it may throw a wrench in the idea. Keep in mind the timelimit option will not stop a running index rebuild. I.E. if you have a time limit of 5 minutes and a rebuild takes 30 minutes, it will still take 30 minutes, the script just won't run the next index operation in the queue. It may stop a REORG in the middle, but I'm not 100% sure on that.

HTH,

Jon

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

My suggestion would be to find out who's placed the limitations and see if they like high velocity pork chops. If they don't see the light, give the job of maintaining the indexes to them... on a high velocity pork chop, of course. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dunno_developer SSC Enthusiast Points: 164 More actions · Answer 7

Hi

Have you considered partitioning your large tables?

Partitioning will allow you to maintain your larger tables as you can target only the partitions which are require maintenance.. (depends what your db is doing though)

(i.e. reduce the amount of data that will need to be rebuilt -> reduced tlog size)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 8

dunno_developer (2/25/2016)
Hi
Have you considered partitioning your large tables?
Partitioning will allow you to maintain your larger tables as you can target only the partitions which are require maintenance.. (depends what your db is doing though)
(i.e. reduce the amount of data that will need to be rebuilt -> reduced tlog size)

That's actually a great idea. Let's hope that the people that place the other limitations on the OP don't reject that idea.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ico-601891 Hall of Fame Points: 3992 More actions · Answer 9

Thank your for all ideas (I'm going to analyze all of them).

Sergiy SSC Guru Points: 110208 More actions · Answer 10

For such occasions I've built a script which checks if TRN log files are filled for >50% and launches corresponding BACKUP LOG jobs when required.

Those jobs may included into replication plans, maybe not - it does not matter.

You just dynamically shorten the interval between consecutive backups when the growth is too big.

_____________
Code for TallyGenerator