0

Is there a way to estimate log file size? I have a scenario where there is a possibility of 40-45K rows per day.

For the above scenario,

  1. What should I consider to estimate log file size?
  2. How much initial size it will be?
  3. On what basis, the log file growth will be there?

Finally, any approximate size for records 40-45K per day?

5
  • based on test data, or historical data is the best way to estimate
    – Hasson
    Sep 5, 2016 at 8:33
  • Could you please provide some tips to estimate on the ways you told?
    – SSS
    Sep 5, 2016 at 8:35
  • can you test your server with say 20K records? then the produced log file for 40K will be double that approximatly ...
    – Hasson
    Sep 5, 2016 at 8:38
  • Thanks! And this is the transactional log file size right?
    – SSS
    Sep 5, 2016 at 8:56
  • Generating test data will not give an accurate measure of log file growth. You need to factor in the recovery model type, how often and by how much the data will change, the average transaction size, backup strategy, etc. There are so many variables involved that the best way would be to extrapolate trends from your live server. Sep 5, 2016 at 10:16

1 Answer 1

1

I think you'll find a good starting point here - http://blog.sqlxdetails.com/set-transaction-log-initial-size/

To summarize from the link:

  • Set your transaction log large enough so it will not grow during normal operation. All update activity stops in the database when the logs grows, so you want to minimize log growths.
  • For in initial log size, you could roughly estimate the log size needed to rebuild your largest table in db (roughly, that is the size of that table) and set the initial log size to that value. That would be the “fit largest transaction” method. Imprecise, but is very simple.
  • Log file growth should be changed from a percentage (as is the default) to a fixed MB size and monitored.
  • The link references the usage of BULK RECOVERY model to limit the size of the transaction log due to 'minimally logged' transactions like BULK INSERT and INDEX REBUILDS. You can only switch to BULK RECOVERY from FULL RECOVERY, but you should be aware that you cannot restore to a point of time that encompasses a 'minimally logged' transaction because individual log (insert/update/delete) records are not generated during that process. While the actual log file will not be large for a 'minimally logged' transaction, the log backup will still be large due to backing up the changed data extents that were affected during the 'minimally logged' transaction.
  • In FULL RECOVERY, the transaction log will continue to grow until a log backup occurs (assuming there aren't any open transactions left)
  • In SIMPLE RECOVERY, the transaction log is 'basically' available for reuse after a transaction commits work.
  • Generally speaking, shrinking the log is discouraged. At some point, there was activity that caused it to grow to that size (index rebuild, etc.). There may be situations where an out-of-the-normal issue occurred and caused the transaction to grow large than normally needed and in those cases you may need to shrink the log down to a more realistic size

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.