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

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?


All of your current tests (DBCC SHRINKFILE, log_reuse_wait_desc) are simply proving that right now you are reusing the transaction log's virtual log files appropriately. But when your auto growth events happen on your transaction log file it's a reaction to the log not being able to be reused.

Oftentimes, that isn't an ongoing condition (exactly how it seems to you right now with the lack of symptoms you're currently seeing). There are a handful of things that could cause this behavior, even in the simple recovery model.

Your best bet would be to setup a data collection job to routinely pull the log_reuse_wait_desc for your database, and routinely log this somewhere. Then you should be able to reverse-engineer what is causing the lack of log reuse.

It keeps saying the log space should be reused, but with this slow growth over the course of months, it doesn't seem that it is.

As I indicated above, it's not typically an ongoing condition that causes the lack of transaction log reuse (save a few corner cases, like poorly constructed transactions), so you'd have to pinpoint the times when this is happening. The lightweight diagnostic data collection should be a good start.