Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Actually, I have observed a similar phenomenon myself at one of my clients' sites databases with SIMPLE recovery model. The logs don't grow (yet, anyway), but the used space in the log files does grow by a tiny bit every night. And it happens when the database backup runs. I have not yet figured out what is causing it nor whether its a problem or not. –  RBarryYoung Aug 13 at 17:49

3 Answers 3

up vote 7 down vote accepted

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?

share|improve this answer
    
I don't think we're doing anything that would require 300MB of log, but even if we did, once it were done, wouldn't it show up as free space on the database? When looking at the properties on the database in SQL Server Management Studio, the size is the size of the data and the log, and I would expect the free space to be the free space on the data and the log. Does free space in the log not show up? That it didn't show as free seemed like it was still in use, but there was no activity on the database. –  DerekCate Aug 13 at 17:07
    
No, once it is done, it doesn't automatically become free space. The committed transactions are not zeroed out, their space is just marked as available for reuse. –  Aaron Bertrand Aug 13 at 17:08
    
@DerekCate "I don't think we're doing anything that would require 300MB of log"... You'd be surprised, it doesn't take much to bind up the reuse of the transaction log. Don't think of it in terms of quantity of workload, as that's not always the cause. –  Thomas Stringer Aug 13 at 17:12
    
Ok, so just to confirm I'm understanding this correctly, the 300MB log, even if not currently needed, will not show as free space, but will be reused. And at some point, it needed 300MB to handle some transaction. Ok, new things to look into. Thank you! –  DerekCate Aug 13 at 17:20
    
Another thing to consider: an automatic checkpoint for a simple recovery database is only queued once the log is already 70% full. So, you might not need all that much log activity to result in growth, depending on timing. –  Paul White Aug 14 at 6:43

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.

share|improve this answer
    
If he is seeing 50MB free and a 300MB log would fn_DBLOG() give him some insight into what was increasing the size of his log? –  Kenneth Fisher Aug 13 at 18:05

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.

share|improve this answer
    
Auto shrink is not enabled on the DBs, we're looking to avoid index fragmentation brentozar.com/archive/2009/08/…. We do have weekly integrity checks, but I don't think there is any index rebuild as part of that, I'll have to look into that. Other than that, no GUIDs, every table has an identity column that is the primary key. –  DerekCate Aug 14 at 12:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.