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

Question

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:

SELECT
    DB.name,
    MF.physical_name,
    MF.type_desc AS FileType,
    MF.size * 8 / 1024 AS FileSizeMB,
    fileproperty(MF.name, 'SpaceUsed') * 8/ 1024 AS UsedSpaceMB,
    mf.name LogicalName
FROM
    sys.master_files MF
    JOIN sys.databases DB ON DB.database_id = MF.database_id
WHERE   DB.name = 'yourdatabasename'

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 https://sqlblog.org/2007/01/11/reviewing-autogrow-events-from-the-default-trace

DECLARE @path NVARCHAR(260);

SELECT 
    @path = REVERSE(SUBSTRING(REVERSE([path]), 
    CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
   DatabaseName,
   [FileName],
    SPID,
    Duration,
    StartTime,
    EndTime,
    FileType = CASE EventClass 
       WHEN 92 THEN 'Data'
       WHEN 93 THEN 'Log'
    END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE
    EventClass IN (92,93)
ORDER BY
    StartTime DESC;

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.

Answer 1

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?

Answer 2

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.

Answer 3

Do you have auto-shrink enabled on the DBs ? And/or do you have scheduled maintenance plans performing index rebuilds ?

An auto-shrink followed by index maintenance will produce significant log volume.

Index maintenance by itself will also product significant log volume, if there are DB design issues like clustered indexes on GUIDs.

Answer 4

 create table #dblog (Databasename varchar(100),
                     logsize float,
                     logspace%] float,
                     [Status] int)

 insert into #dblog
 EXEC ('DBCC sqlperf(logspace)') 

 select * from #dblog

 alter table #dblog 
 add [lgspace used GB] float;

 update #dblog 
 set [lgspace used GB ] = (logsize*[logspace%]/1024)

 update #dblog 
 set [logsize] =([logsize]/1024)

 alter table #dblog 
 drop column [Status];

 select * from #dblog

 drop table #dblog