Change Log Backup Frequency Automatically

  • Neil Burton

    SSC-Insane

    Points: 22228

    During our weekly index rebuilds (using Ola Hallengren's scripts), the log expands considerably.  This isn't unexpected and there's plenty of space into which it can expand.  I do have a task to look at minimising the growth but it's behind other things, including mitigating the growth.

    The problem we actually have is further downstream.  We have hand-rolled logshipping in place to keep some standby reporting databases in sync.  It uses a set of PowerShell scripts to copy the log backups from the network share, restore them and then clean up.  It usually works seamlessly but the weekend before last we had a disk space error on the reporting server.  The ultimate cause of this was a 55gb log file that took 20 minutes to restore.  This was greater than the 15 minute query timeout in the PoSh script and so it failed and the restore was rolled back.  This meant that subsequent log files couldn't be restored, so they couldn't be deleted and so we ran out of space.

    The quickest fix here was obviously to extend the timeout.  After a bit of discussion, we thought that better than getting rid of it altogether.  However, we're still getting the huge log files and we're concerned about hitting the new 30 minute timeout.  Unfortunately changing the recovery model during the rebuilds isn't an option.  We provide a managed service to a third party and what we do is 24 hour so there's always something happening on the servers.

    What I'm thinking of doing next is to take the log backups more frequently during the index rebuilds.  We currently backup every 15 minutes and I'm considering doing it every 5 minutes.  My plan is to have a job running at 0355 on Sunday which runs sp_update_schedule to change the interval to 5 minutes and starts a WHILE loop to check whether the index rebuild is still running.  When the rebuild job is finished, I'll run sp_update_schedule again to set it back to 15 minutes.  Is this a wise idea?

     


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Grant Fritchey

    SSC Guru

    Points: 396617

    Honestly, testing and experience is going to be the answer. Does it sound like a reasonable approach to me? Yes.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Neil Burton

    SSC-Insane

    Points: 22228

    Thanks Grant.  The next day or so is going to be spent testing things.  I want to make sure I'm not planning something completely stupid before I spend any more time on it though.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Grant Fritchey

    SSC Guru

    Points: 396617

    I hear you. Thinking about it some more, while a bit of a pain, I don't see any way this is dangerous. Sure, you have a few more files, so any recovery action involves additional files. That may add a tiny amount to the recovery time, but not very much. You have more files to manage, again, slight pain, but not real pain, like timeouts. I think it seems reasonable. Although, as with ALL things, testing is your buddy.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • tripleAxe

    SSCertifiable

    Points: 5605

    You could also configure a SQL Server Agent Alert to run a transaction log backup job when more than a specified percentage of the log is used.   I have done this for a similar situation to yours.  Normally the log shipping transaction log backup runs every 15 minutes but during the index rebuild job it runs more often as the log file starts using more than 20%.

     

  • Neil Burton

    SSC-Insane

    Points: 22228

    tripleAxe wrote:

    You could also configure a SQL Server Agent Alert to run a transaction log backup job when more than a specified percentage of the log is used.   I have done this for a similar situation to yours.  Normally the log shipping transaction log backup runs every 15 minutes but during the index rebuild job it runs more often as the log file starts using more than 20%.

    That's interesting.

    Are there any big advantages to setting up the alert?  I've already put the paperwork through to change the schedule and that took a bit of a sales pitch.  I don't want to sound like I'm writing off your suggestion but I think it would need to be a much better approach to change direction now.

    Of course, in future it's definitely something worth bearing in mind.

     

     


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • tripleAxe

    SSCertifiable

    Points: 5605

    You wouldn't need to change the schedule and if the log file started to fill up beyond 20% (or whatever threshold you set) the tlog backup job would automatically run.

  • Neil Burton

    SSC-Insane

    Points: 22228

    I like the idea of it picking up automatically, I must say.

    It's an out-of-the-box method too and I think the higher-ups will like that.  It might be time for a bit of a discussion amongst the team.

    We're about to start reviewing backup and restore policies and making sure things are standardised as far as possible.  This could be something we add then.

     

    • This reply was modified 6 months, 4 weeks ago by  Neil Burton.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Jeff Moden

    SSC Guru

    Points: 996832

    I have my production databases set to be "exposed" to the possibility of a transaction log backup every 15 minutes, but it's stupid to do an actual T-Log backup if nothing has actually changed.  When you do your laundry, do you wash the clean clothes as well as the dirty ones?

    sys.databases has a column called "log_reuse_wait" for every database.  If it contains the value of "Nothing", then don't do a backup.  It's that simple.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996832

    p.s.  And stop using REORGANIZE for your index maintenance.  It doesn't work the way most people think (actually sets up indexes for perpetual fragmentation) and it's not the tame little kitty people think it is, especially when it comes to the burden on the log files.

    For example, I did a test where I restored a database to a test box.  I did a REORGANIZE on a 146GB Clustered Index that was "only" 12% fragmented.  It took 1 hour and 21 minutes and caused the log file to explode to 227GB from 37.

    I repeated the test using the same restore but did a REBUILD in the full recovery model.  It also took 1:21 but the logfile only grew to 146GB.

    If you don't have anything dependent on being in the FULL recovery model, you'll also be pleased to know that if you can temporarily shift to the BULK LOGGED recovery model (and, yeah... it take DIF backups before doing so), the index rebuilt in only 12 minutes and there was almost no log file growth.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Neil Burton

    SSC-Insane

    Points: 22228

    Jeff Moden wrote:

    I have my production databases set to be "exposed" to the possibility of a transaction log backup every 15 minutes, but it's stupid to do an actual T-Log backup if nothing has actually changed.  When you do your laundry, do you wash the clean clothes as well as the dirty ones?

    sys.databases has a column called "log_reuse_wait" for every database.  If it contains the value of "Nothing", then don't do a backup.  It's that simple.

    That makes total sense.  However we're a truly 24 hour business and the database in question is one of our busiest.  If nothing changed for more than a few tens of seconds I'd be surprised.  If nothing changed for a couple of minutes, the users would be on the phone.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Neil Burton

    SSC-Insane

    Points: 22228

    p.s.  And stop using REORGANIZE for your index maintenance.  It doesn't work the way most people think (actually sets up indexes for perpetual fragmentation) and it's not the tame little kitty people think it is, especially when it comes to the burden on the log files.

    That is worth knowing.  I was surprised to see such high growth for a REORGANIZE.  The numbers I was seeing were proportionally in the same ballpark as you got.  It was a smaller index but but the changes were in similar ratios.  Unfortunately, because I'd found a 'solution' further investigation was stopped from above.

    How does that fit in to the Ola Hallengren scripts though?  As I said above, we're looking at standardising the various tasks across the estate and part of the standardisation is to makes sure we're using them everywhere. If there's a case for saying don't, then I'm quite happy to make it. Apparently if you use 'the internet' you can find this sort of stuff out for yourself.  What will they think of next?

    With regard to the recovery  model, I'd forgotten about Bulk-Logged.  It's definitely something to consider.  Another part of the standardisation is to review the backup policies and procedures so there's an opportunity to look at that.

    • This reply was modified 6 months, 3 weeks ago by  Neil Burton.
    • This reply was modified 6 months, 3 weeks ago by  Neil Burton.
    • This reply was modified 6 months, 3 weeks ago by  Neil Burton.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Thomas Rushton

    SSC-Insane

    Points: 22648

    A couple of thoughts...

    1. A job can have multiple schedules.  At one place, I had a similar problem, so I put a secondary schedule on the TLog backup schedule job to run every minute starting at the same time as the index job began and ending two hours later.  (That two hours was the average plus a bit duration of the index maintenance job.)
    2. Why not have the sp_update_schedule steps built into the index maintenance scheduled task?

     

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • Neil Burton

    SSC-Insane

    Points: 22228

    I somehow managed to miss having more than one schedule for a job.  All the research I did was about varying the schedule rather than having more than one.  The scheduling did seem less flexible than it could have been.  There was an admitted failure of the Google-fu though.

    The thinking behind having a separate job was to keep the maintenance jobs the same across the servers.  We only needed to vary the schedule on of the servers so we decided against having one job that was different from the others.

    There's quite a bit to think about here.  But as I said, I've got a solution so there's no need to do any more investigation...


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Grant Fritchey

    SSC Guru

    Points: 396617

    Jeff Moden wrote:

    p.s.  And stop using REORGANIZE for your index maintenance. 

    With the noted exception of columnstore indexes, but only when working in 2016 SP1 or greater.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 15 posts - 1 through 15 (of 21 total)

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