0

As the title states, this is most likely going against best practices but I want to hear some opinions on this.

The database(s) in question are in FULL RECOVERY mode, transaction log backups are being performed every 15 minutes. Maintenance tasks are being performed to Microsoft and Microsoft MVP standards.

We have an ERP system that really uses SQL inefficiently. The database size is relatively manageable, but the log file continuously grows out of control. In the past we've just allocated space to the log file drive in order to control growth. I understand I can limit growth, but our ERP vendor doesn't recommend this approach. Month end processing can make this log almost double in size and make our transaction log backups huge. No problem, right? Well it's a problem as we now have two databases doing this same thing on month end.

To make a long story short, I want to have a discussion on the pros and cons of going against the grain on backing up the transaction log file, then shrinking it to conserve space. I've tackled this processes a couple times out of the year to manage space and get the logs under control but I want to hear some opinions on automating this process to relieve some headaches or to make me think outside the box.

What is recommended in this scenario that I'm not doing already? I'm to the point of offloading transaction log backups every minute to try and further prevent the log from growing but I'm not too hopeful that will solve the problem.

  • Do the two log-intensive month-end process run at different times? If so, I can maybe see running one and then truncating to make space so the second can run. However, as long as the first process completes and checkpoints before the second starts, the log space should be available for the second process. Why release the space to the OS in between when you know you'll need it again anyway? – Jon of All Trades Sep 26 '16 at 16:34
  • Actually how big does the log file get during the month-end process? – Max Vernon Sep 26 '16 at 17:48
4

I'm assuming you have these databases in FULL RECOVERY because you need to be able restore to a point in time. If that's NOT the case, then use SIMPLE mode and your log would only grow to the size needed to contain a transactional unit of work (which could be index maintenance as well as application transaction).

Now, back to the FULL RECOVERY model. Backing up the log on a periodic cycle (whether it's 1 minute or 15 minutes) is only going to help you if transactions are able to commit during that time frame. Active long running transactions will prevent that log space from being flagged for reuse.

There shouldn't be a problem with running transaction log backups every minute (Back Up Transaction Logs Every Minute. Yes, Really).

Constantly shrinking the log will have a negative performance impact due to waiting for the log to grow again. Logs do not honor Instant File Initialization.

The bottom line is:

The transaction log has to be large enough to hold an active unit of work and you need timely log backups to allow of log reuse.

1

I'm not sure what "Microsoft and Microsoft MVP standards" are, but my recommendation would be to find yourself some extra storage and to grow those logs out, and let them be at the larger size.

Shrinking the logs is going to significantly slow down your month end processing, as each growth of the log is going to cause a stall in the processing while the new VLFs are zeroed out. Depending on the performance of your storage a 1GB log file growth could take 1-20 seconds (or maybe even longer). I'm pretty sure everyone would be super happy if it meant that the processing was completed earlier than it is now. There's the additional concern that your log shrinking routine would make the log smaller than it needs to be for general (not month end) usage. That would mean that you would have impact during regular work at those times when the log needs to grow.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.