1

The log file for my SQL Server 2014 database is growing rapidly (currently in 32 GB in COMPLETE RECOVERY mode). This is creating storage issues on my server.

The database has a set of job's with large SELECT - UPDATE - DELETE from another DB occurring at different times of the day.

  1. How do I avoid rapid growth of the log file?
  2. What practices should I use to minimize log growth?
  3. COMMIT TRANSACTION is generally not used in STORE PROCEDURES, is it essential to add them as part of improvement?
  4. Researching I read that I should use TRUNCATE TABLE, WITH (NOLOCK) as part of optimization, I would like to know your opinions.

Sorry for my English, thank you for your support.

4
  • 2
    What do you call "accelerated increase"? The problem could be the frequency of your log backups and not the log growing fast. How often do you backup the log? – Ronaldo Aug 7 '20 at 19:11
  • 1
    Back up the log more often? Or switch to simple recovery? And/or batch large operations? – Aaron Bertrand Aug 7 '20 at 19:41
  • My database run job with operations loaded and that seems to make my log grow considerably. Currently it is already in 32 GB and my task is to optimize its performance, is it advisable to go to simple recovery? – Robinson Ascencio Peña Aug 7 '20 at 21:53
  • We can't make a decision like that for you. Please read this question and its answers in full. – Aaron Bertrand Aug 8 '20 at 0:19
2

The most important consideration is to pick the right database recovery model that meets the business needs. If you're using a recovery model of full then you also need to backup the log on an appropriate frequency. There's also a question of scale here. If your log is 32 GB in size for a 10 GB database then I understand why you're considered. However, if that log file is for a 10 TB database then the right thing to do might be to allocate more space for the transaction log files. Be careful in how you think about the log files. It's a good thing that protects against data loss. It shouldn't be viewed as your enemy that should shrunk as much as possible.

With all of that said, there may be different ways of writing code which lead to the same results but reduce total writes on the transaction log file. Taking advantage of possible optimizations in this space can improve performance. Some high level tips:

  • If your business needs allow you to change the recovery model to simple then some code may qualify for minimal logging.
  • There may be an opportunity to change code to use temp tables instead of user tables. Tempdb always has a recovery model of simple so you can get minimal logging for those operations.
  • Your application design may allow for the use of a scratch database to hold temporary data, such as a staging database. Your business requirements may allow the staging database to have a recovery model of simple while keeping the recovery model of the real database as full.
  • Getting data Right The First Time can reduce log writes. For example, a single INSERT instead of an INSERT + UPDATE will often write less to the log. Same concept applies to avoiding operations which delete large numbers of rows, if possible.
  • SQL Server acquired locks need to be written to the transaction log as well. In high scale scenarios you may see an improvement by adding TABLOCK hints to reduce transaction log activity.

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.