My Favorite System Column: LOG_REUSE_WAIT_DESC

Hidden away in master.sys.databases is one of the coolest diagnostic tools you might ever need:

This spiffy little snippet will tell you why each database’s log file isn’t clearing out. Possible reasons include:

  • Log backup needs to be run (or if you could lose a day’s worth of data, throw this little fella in simple recovery mode)
  • Active backup running – because the full backup needs the transaction log to be able to restore to a specific point in time
  • Active transaction – somebody typed BEGIN TRAN and locked their workstation for the weekend
  • Database mirroring, replication, or AlwaysOn Availability Groups – because these features need to hang onto transaction log data to send to another replica

That’s it. That’s the whole blog post. Listen, they can’t all be 2000-word masterpieces.

Previous Post
Moving Databases with ALTER DATABASE
Next Post
Changes to auto update stats thresholds in SQL Server 2016

36 Comments. Leave new

  • Will this work for tempdb log is that a different beast?

    • Tim – sure, works the same way. If you want to find out which open transaction is doing a lot of work in TempDB, check out sp_WhoIsActive.

  • Lee Steffensen
    March 3, 2016 11:18 am

    So when I run this I see that tempdb is showing ACTIVE_TRANSACTION. Should this be cause to worry and investigate?

  • Curious. I run this against my development instance and it reported that my current project’s database, which is not transactionally active and I’m the only user, says Log_Backup. I waited until after the next 10 minute log append backup runs, and it still says Log_Backup. I re-ran the nightly backup and it went back to Nothing. OK. But when I ran the log initialization backup, it went back to Log_Backup.

    So I’m not sure what value this represents for me. I can see that if some of the values that it can report are shown, then that could be a good troubleshooting indicator. But the Log_Backup flag doesn’t make sense to me.

  • Dang it Brent! I look forward to your novella-length masterpieces, I even have a couple printed and framed!
    🙂

    Log_reuse_wait_desc once led me down the rabbit-hole of resolving a database showing Replication as the wait type, when there’s no replication configured. That was *loads* of fun…

  • Hi Brent – With AlwaysOn the log reuse can be held if say the replica is paused or down or maybe just catching up with a big operation. So primary retains log until back and/or synced. Correct..? Does the availability mode (sync or async) have any impact on this.

    I.e. if I’ve a large archive running on primary that generates a lot of log records. I want my primary to be able to truncate (overwrite) log once its already sent log to secondary but not wait until committed on secondary. Because secondary may take a while sync… Causing excessive log growth on primary because it has to wait…

  • Hi,I have a database in simple recovery model and the transaction log keeps growing.I shrink it every day with a job but the point is to find why this happens and how to solve it.shouldt the log “forget” any commited transactions since the database is in simple.

    • Kostis – make sure to read the post, that’s what it’s all about. For example, someone could have an open transaction, or you could have a broken replication setup.

  • Jessica Dellario
    May 8, 2018 9:26 am

    I am confused on how to write this to make it work. I’ve copied it and changed the word NAME to the name of the database but it keeps returning “Msg 207, Level 16, State 1, Line 1, Invalid column name ‘SysproCoM’.”
    What am I doing wrong?
    Thanks

    • Jessica – try putting the database name inside single quotes, maybe?

    • Jessica Dellario
      May 8, 2018 12:06 pm

      Never mind, I figured it out. I had to include single quotes around the database name for it to work.
      Thanks

      • Jessica Dellario
        May 8, 2018 12:08 pm

        Funny – I refreshed this 10 times before going to lunch and your reply wasn’t there. I posted my reply and now your’s is there. Time to reboot. LOL

  • I run this and see a database that says “ACTIVE_TRANSACTION”. When I use dbcc opentran(‘dbname’) it shows nothing. When I select * from sys.dm_tran_active_transactions I get six rows with the name column having “worktable” – I’m guessing that’s a temp-table? – and the transaction_begin_time says 2018-08-16. This is a database we get every morning from a vendor as a .bak and we do a restore. How can there be an open transaction from 8/16 when we restored it this morning 8/27? How can I find out which user/process is causing the transaction? Thanks!

  • Hello Brent,

    log_reuse_wait_desc is shown as nothing for cdc database. what could be the problem and fix. can u plz help

  • What if AVAILABILITY_REPLICA frequently shows up? Would that be cause for concern?

  • Ajith Bhojani
    June 25, 2019 7:39 pm

    Is there a way one could setup a job to check this and if a tranny log hasn’t been back’d up for a while sends out a notification?

  • Vinicius Lana
    June 28, 2019 6:02 am

    How do I solve this?

  • Vinicius Lana
    June 28, 2019 6:03 am

    How do I change the tempdb from ACTIVE TRANSACTON to NOTHING?

  • most of the time i want to use your website but i failled.

Menu
{"cart_token":"","hash":"","cart_data":""}