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 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?
(edit) The After Action Report - AKA a little knowledge is dangerous
After finding that this is a "popular question", it felt like I owed an explanation of what happened 7 months ago and what I learned to hopefully save some other people some grief.
First off, the space available you see in SSMS when you view the properties on a database is the space available in the data file. You can view this by running the following on a database, and you'll find the space available reported by SSMS is the difference between the FileSizeMB and the UsedSpaceMB:
This did confirm that under normal circumstances we were using very little log space (20MB or less), but this leads into the second item.
Second, my perception of the logs growing was that of slowly over time. However, in reality the logs were growing rapidly on the nights the guy responsible for applying patches for this 3rd party application was applying patches. The patch was done as a single transaction, so depending on the patch the 200MB data needed 300MB of log. The key in tracking that down was the query from Aaron Bertrand at http://sqlblog.com/blogs/aaron_bertrand/archive/2007/01/11/reviewing-autogrow-events-from-the-default-trace.aspx
This showed that log was growing on certain evenings, when the customer wasn't using the database. That led to the conversation with the guy applying the patches and the answer to the mystery.
Thanks again for people who provided help to get me to the answer.