Why does transaction log continue to grow in Simple recovery mode with nightly backups

Before immediately marking as duplicate, I have read Mike Walsh's Why Does the Transaction Log Keep Growing or Run Out of Space?, but I don't think it gave an answer to my situation. I looked through a dozen or so similar questions, but the relevant ones mostly just said "duplicate" and pointed to Mike's question.

Details: I have a bunch of ~500MB databases on SQL Server 2008 R2, all in SIMPLE recovery mode (not my choice), nightly full backups, with ~200MB data files and ~300MB log files. The log doesn't grow to 300MB immediately, but rather slowly over the course of a couple months. There are no open transactions on any of them, at least according to sp_who2 and the activity monitor. If I right-click on the database and select properties, it tells me there is ~50MB free. Particularly right after a backup, shouldn't the whole log be free? In SIMPLE mode shouldn't the log be free as long as there isn't an open transaction?

log_reuse_wait_desc from sys.databases says says "NOTHING", which based on the question and answer referenced above says it shouldn't wait on anything to reuse the space.

If I do 'DBCC SHRINKFILE', the log file shrinks to 1MB, so it is willing to reclaim the space. I can set something up that shrinks the logs weekly and keep things from getting out of control, but I'm confused as to why SQL Server would make me do that.

I can understand if there was some crazy transaction that needed 300MB to log it, but we're not doing anything extreme, just basic OLTP. From Mike's question/answer:


Simple Recovery Model - So with the above introduction, it is easiest to talk about Simple Recovery model first. In this model, you are telling SQL Server - I am fine with you using your transaction log file for crash and restart recovery (You really have no choice there.. Look up ACID properties and that should make sense quickly), but once you no longer need it for that crash/restart recovery purpose, go ahead and reuse the log file.


SQL Serv

It is impossible for us to guess what is causing it, but SQL Server doesn't just grow a log file to 300 MB for the heck of it, it grows to 300 MB because at some point since your last shrink operation, it needed that much log space (whether due to some big single transaction or a lot of smaller concurrent ones). You'd have to trace log file growth events (I talked about this here and here) to try and narrow down when or why this happens (also if you log file growth setting is 300 MB or something, then it will grow by 300 MB as soon as it needs more than 1 MB of space to accommodate active transactions).

Anyway, why do you think you need to shrink the log file once it has reached 300 MB? Did you actually read all of the answers, thoroughly, on Mike's question? The log file is NOT going to shrink on its own, because shrinking the log file to 1MB - just so it can grow again during your largest transactions - is a total waste of time. What are you going to do with all of that free space in the meantime?

StackExchange Database Administrators Q#73921, answer score: 20