Home / server / Questions / 50456
In Process
Will
Asked: 2009-08-06 08:15:02 +0800 CST

Transaction Log filling up on SQL database set to Simple

  • 772

We have a database on a SQL 2005 server that is set to Simple transaction mode. The logging is set to 1 MB and is set to grow by 10% when it needs to.

We keep running into an issue where the transaction log fills up and we need to shrink it. What could cause the transaction log to fill up when its set to Simple and unrestricted growth is allowed?

sql-server sql-server-2005

3 Answers

  1. There are a few things that can cause the log to have to grow, even in the SIMPLE recovery model:

    • A long-running transaction - the log cannot be cleared until the transaction commits or rolls back. You can use DBCC OPENTRAN to show you the oldest active transaction.
    • Transactional replication - the log cannot be cleared until the log reader job has read the committed transactions

    There was also a bug in SQL 2000 SP4 that prevented checkpoints from properly clearing the log - see my blog post for more details: Why won't my log clear in SIMPLE recovery mode? SQL 2000 bug or very large VLFs.

    My guess is that you've got a long-running transaction.

    You shouldn't need to keep shrinking the log - constantly shrinking and growing the log leads to a thing called VLF fragmentation, which can affect performance. Also, whenever the log grows it must be zero-initialized, which causes everything to wait while the initialization takes place. Let the log reach a steady-state size and leave

    Checkout the long article I wrote for TechNet Magazine on understanding the log and how it behaves in the various recovery models: Understanding Logging and Recovery in SQL Server.

    Hope this helps!

    • 7

  2. I realize that this is an old post, but I just found this while attempting to find out more about this exact same issue and this is still the best discussion of its kind. It's helped me immensely.

    According to Paul Randal, the log file is in reality composed of Virtual Log Files at 512KB each. Robert, on the other hand, said that a 5MB initial log size works fine with 10% increments. So that got me doing some simple math.

    5MB = 5120KB. 10% growth from 5120KB is exactly 512KB, or 1 VLF's size! With Will's log file of only 1MB (or 2MB in my case), 10% growth results in attempts to grow by 102.4KB (or 204.8KB for me), which is smaller than even 1 Virtual Log File! I believe this is the reason the log can't grow! Robert's solution to increase the initial size of his log helped start up the increments. Another solution (not tested, but I bet would work) would be for Will to increase the increments to 50%, or for me to 25%. Of course, I would not recommend this because even if it means a mere 512KB growth the first time around, it can cause some tremendous increase later. The other alternative is to set the growth to in 1MB increments. Given that we expect small logs with slow growth, that may be a good solution. Naturally, if the expected growth is much bigger, such a small growth would mean that the logfiles need to be grown too often, which will affect performance. Either way, any of these solutions should work well in avoiding 9001/9002 errors with Unrestricted Growth, on servers that have plenty of free HDD space.

    • 2

  3. I am having this same issue in SQL 2005, log file is set to unrestricted growth, yet it doesnt grow, when it fills up I get a 9001 error. The solution for me was to set the initial log size to 5 mb, seems to have cleared up the issue.

    • 0