1

This question already has an answer here:

We have a SQL database on FULL back up with a client and they have NOT implemented a Log file maintenance and noticed that the log file grows exponentially, filling the 20GB they have allocated to it in a matter of days.

Once they switch into 'maintained log file mode' this problem goes away.

As we are not SQL experts, I am writing to you as to whether exponential growth is normal if you do not maintain the log file.

Thanking you in advance,

Steve

marked as duplicate by Max Vernon, Kenneth Fisher, RolandoMySQLDBA, mustaccio, Tom V - Team Monica May 31 '16 at 20:12

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    What is maintained log file mode?. – Shanky May 31 '16 at 10:33
  • Sorry ...I meant ' A transaction log back up plan is put is place....' – Steve Adams May 31 '16 at 10:56
  • Having read that post I can see your point, but I am simply asking if exponential growth could be 'expected ' if t-log backups were not taken ... ? – Steve Adams May 31 '16 at 13:49
4

To answer your question, in FULL recovery mode, growth of the log file is absolutely normal, and of course it will grow to either the limited size or the size of the disk unless it is maintained. The rate of growth will be determined by the type of / frequency of / load of transactions hitting your database. The rate of growth, will also determined by your auto-growth settings, i.e. grow by fixed value or percentage etc.

Logs are typically maintained by performing a log backup at suitable intervals. This truncates the data within the log file itself allowing the space to be re-used for new transactions.

What is important to note is that the problems you might be facing due to a large log file (i.e. where the size has gotten out of hand due to lack of suitable maintenance) is not necessarily remedied by a transaction log backup alone. You may need to shrink the file to free up disk space, for example.

  • Thanks for the fast response. Yes, once maintained, all is well. We just need a informed opinion on whether exponential growth is normal if the log file is left 'unmaintained' ? – Steve Adams May 31 '16 at 10:54
  • But would you expect EXPONENTIAL growth if unmaintained ? (we saw 10%, 20%, 200%, 1300% etc ) – Steve Adams May 31 '16 at 10:58
  • the setting is 10% limited to 20GB .... – Steve Adams May 31 '16 at 11:15
  • Ok, thanks. But that would be 'expected', not the 500%, 1500%, type growth that we were seeing overnight... – Steve Adams May 31 '16 at 11:24

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