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?
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 Server listens to this request in Simple Recovery and it only keeps the information it needs to do crash/restart recovery. Once SQL Server is sure it can recover because data is hardened to the data file (more or less), the data that has been hardened is no longer necessary in the log and is marked for truncation - which means it gets re-used.
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.
What am I missing? Is something keeping SQL Server from recognizing the data as "hardened" and freeing up the log?