4

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?

Will
  • 826
  • 2
  • 9
  • 19

3 Answers3

7

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!

Paul Randal
  • 7,194
  • 1
  • 36
  • 45
  • Thanks for the information! What I don't understand is why the transaction log isn't growing like it's supposed to. Even though it's set to unrestricted growth, it's never growing past 1 MB. – Will Aug 05 '09 at 17:24
  • So do you mean that the database stops with a 9001/9002 error because the log can't grow? Or that you just never see it growing past 1MB? Do you have a max size set for the log? – Paul Randal Aug 05 '09 at 18:00
  • Yes, it is stopping with a 9002. It is currently set to grow by 10%, unrestricted, no max. And there is plenty of drive space. – Will Aug 05 '09 at 18:51
  • Does it happen all the time, or just occasionally? Do you have auto-shrink enabled? – Paul Randal Aug 05 '09 at 19:18
  • It's happened 4 or 5 times in the past week. Auto-Shrink is not enabled. – Will Aug 05 '09 at 19:23
  • Can you manually grow the file? And how big is the database itself? – Paul Randal Aug 05 '09 at 19:26
  • Do you have the query timeout set really low? Could be that the log can't grow within the query timeout - unlikely though for a log this small. – Paul Randal Aug 05 '09 at 19:29
  • We're talking a really small DB with very little going on. 3 MB database. Query timeout is default. We haven't tried to manually set a higher minimum size, I've been more interested in why such a simple DB is getting a full MB in transaction logs under Simple mode and why the log isn't growing like its supposed to on top of that. – Will Aug 05 '09 at 19:38
  • As far as the 1MB thing is concerned, you'll have two VLFs in the log (virtual log files), each 512K. As soon as you have a transaction that spans both of them and hasn't committed, the first VLF can't be re-used and so the log will have to grow. Maybe an index rebuild or a large update operation? Ah - did the SQL service account change since the DB was created? I bet it doesn't have permissions to create or expand files in that directory and that's why the expand is failing. – Paul Randal Aug 05 '09 at 22:26
  • We have 50 other DBs on the server none of which have this problem. Just to be sure I double checked the permissions and they are fine. This one's definitely a little tricky :) – Will Aug 06 '09 at 01:39
  • Hmm - then all I can think of is that the query is timing out and that's cancelling the autogrow, which is taking a long time for some reason. By default, do you mean 30 seconds? And can you try manually growing it - if that doesn't work that will narrow down the problem. – Paul Randal Aug 06 '09 at 15:25
  • Do you have more than one log file configured? – SuperCoolMoss Aug 06 '09 at 20:39
  • We can increase the minimum size, it expands with no problem. The remote query timeout is 600 seconds. We are not using multiple log files. – Will Aug 07 '09 at 01:11
  • ok - in that case I'm stumped. Ran it past Kimberly too and she can't think of anything else. Suggest you call in to Product Support. – Paul Randal Aug 07 '09 at 15:45
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.

Paris
  • 21
  • 1
0

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.