Survey: what is the most worrying cause of log growth?

Time for another fun survey, this time around factors causing the transaction log to grow.

Imagine you’re the DBA for a company and you come to work Monday morning and find that the transaction log on one of the most critical databases, which has a 24×7 workload, is growing repeatedly. You check the log_reuse_wait_desc field in sys.databases to get an idea what the problem is.

Without any other information, which of the following values for log_reuse_wait_desc would you be most concerned to see for this database? (And these are listed in the order they appear in Books Online…) Give it some serious thought – and look at the results of the previous survey I did about wait stats to help guide your thinking, as the most common value you’ve seen in your environment isn’t necessarily the most worrying one.

Edit: the survey is over – please see here for the results.

(Note – if you’re using Internet Explorer, you may not see a Vote button. Just hit Enter after selecting your choice.)

Once again it’s deliberate that there is no ‘it depends’ option, and no ‘Other’ option for you to type something, so please don’t leave comments complaining about that.

I’ll editorialize the results next week, along with what my pick would be and rationales for picking each of them.


11 thoughts on “Survey: what is the most worrying cause of log growth?

  1. Now I’m obsessed about getting the “right” answer, which is ridiculous. Better to shoot for a strong, defensible answer and if there’s good reasoning for another answer being better, recognize that and learn. No one has thought every problem from every angle.

    But still… :-)

  2. I’d be most worried with NOTHING, because the others I’ve come across in this situation, LOG_BACKUP and ACTIVE_TRANSACTION, point to relatively simple solutions.

  3. Is it possible to identify why a log file has grown retrospectively? I.e. After having identified that a transaction log has grown (presumably due to a long running transaction or something similar) is there a way to determine what caused it to grow, and how many times it has grown etc. to reach its current size? Similarly, what would be the best way to determine the required size for a transaction log for a particular database? I usually estimate this at about 10% of the size of the database, though I’d like to be able to determine this more accurately, as this can often be either too large or too small.

    Sorry if this is covered somewhere, but I haven’t been able to find it; the closest I’ve found is info on finding out why it is growing as it happens, or setting up a job to report on the size of it periodically.

    1. There are 3rd Party Tools such as Spotlight from Quest Software that monitor numerous metrics of SQL and Windows Server, including file sizes, and allow to see when a growth occurred and what transactions were going on at the time.

      1. Yup – I prefer Performance Advisor from SQL Sentry (a partner of ours) because of its low impact on the server compared to the other vendor’s tools. Version 8 was just released yesterday.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.