Hidden away in master.sys.databases is one of the coolest diagnostic tools you might ever need:
|
1 |
SELECT name, log_reuse_wait_desc FROM sys.databases; |
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.
53 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.
So when I run this I see that tempdb is showing ACTIVE_TRANSACTION. Should this be cause to worry and investigate?
Lee – no.
Hi Brent,
I have a Job failure with log for ‘tempdb’ is full error & the LOG_REUSE_WAIT_DESC value is ” ACTIVE_TRANSACTION”.
What does this mean? Please guide.
Sandeep –
It means there’s a transaction open using TempDB and filling it up.
Sincerely,
Brent
How do I solve this?
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.
Hi Wayne,
Paul Randal has a great post that goes into details about the Log_Backup description. The short answer from his post is
“If you have a transaction log that is not growing, and you’re taking regular log backups, but the log_reuse_wait_desc remains LOG_BACKUP, this is because zero VLFs were cleared when the previous log backup was performed.”
Read the full post here: http://www.sqlskills.com/blogs/paul/why-is-log_reuse_wait_desc-saying-log_backup-after-doing-a-log-backup/
Wayne – that means you’ve had at least one transaction since the last log backup.
Dear Brent
I am having the same issue. I just completed running a maintenance job which truncates the TLs and in the log_reuse_wait_desc is still says LOG_BACKUP. Why?
Kal – that means you’ve had at least one transaction since the last log backup.
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…
I have the same problem. How did you solve it?
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.
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?
Never mind, I figured it out. I had to include single quotes around the database name for it to work.
Thanks
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!
Brent here – I wish I could do personalized Q&A support on comments, but your best bet is to go to a Q&A site like https://dba.stackexchange.com.
No problem, 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
That means there’s not a problem.
What if AVAILABILITY_REPLICA frequently shows up? Would that be cause for concern?
Tim – I wish I could do personal consulting here in the comments, but for questions, your best bet is a QA site like DBA.stackexchange.com.
Hi Tim,
I think you are referring this error “The transaction log for database database_name is full due to AVAILABILITY_REPLICA”. means log changes in Primary replica has not applied to the Secondary replica. So Primary replica is holding up the transaction till everything has transferred completely on secondary replica.
For more, refer to microsoft article
https://support.microsoft.com/en-sg/help/2922898/error-9002-the-transaction-log-for-database-is-full-due-to-availabilit>
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?
Ajith – sure.
How do I solve this?
How do I change the tempdb from ACTIVE TRANSACTON to NOTHING?
That means there’s an active transaction using TempDB.
most of the time i want to use your website but i failled.
OK, cool.
Transaction log keeps growing and cannot be shrunk after taking the database out of availability group. when I check the log_reuse_wait_desc, it says “availability replica”. but the Database has been removed from AG. I am confused and not sure what to do at this point, I am on SQL 2019 CU4
Tried the usual – log backup –> shrink changed to simple and back does not work.
Bala – for personal consulting, click Consulting at the top of the screen.
Solved – Adding the database back to the AG group (No need to add all the replica nodes) and then removing it again freed whatever was stuck and made the server thinking it is replicating. Log_reuse_wait_desc change to Log_backup and taking a log backup changed it to Nothing and then shrink worked.
I have LOG_BACKUP , but I just did a backup and a tran log backup , plus I have the message he transaction log for database ‘xxx’ is full due to ‘XTP_CHECKPOINT’. help I am desperate
For urgent production support questions, your best bet is to contact Microsoft.
thank you, I resolved adding a second big transaction log to the database
Thanks for this post. It helped us to point long running full backup as a source for LOG BACKUP log_reuse_wait_desc.
You’re welcome.
HI , Brent, I had Database_mirroring in log_reuse_wait_desc status, and in primary sql server errorlog there is Database mirroring connection error 4 ‘An error occurred while receiving data: ‘10054(An existing connection was forcibly closed by the remote host.)’.’ for ‘TCP://, and in secondary sql error log i get “The mirroring connection to “TCP://primaryserverEQDN:5022” has timed out for database “DatabaseName” after 10 seconds without a response. Check the service and network connections.
Please advice how to resolved this?
For personal advice on production servers, click Consulting at the top of the screen. Cheers!
so are you saying that if a database is in an AOAG that the log would never truncate following a tlog backup?
I’ll turn the question around on you: if the primary still has logged transactions that haven’t been sent to an async secondary yet, can we truncate the transaction log?
If the primary replica is configured for asynchronous-commit mode, it does not wait for any secondary replica to write incoming transaction log records to disk (to harden the log).
Right, but can we erase the log if it contains things we haven’t sent to the secondary yet?
No, but is there any reason it will not after all data is sync’d over? Thats my issue, its sync’d, no open trans, absolutely nothing going on and in log_backup mode yet still not truncating…am I missing something?
How does SQL Server know what data to sync, if it’s not logged?
I hate to be tough here, but you’re missing a few basic concepts around logging and AGs, and it’s beyond what I can teach quickly in comments.
[…] SQL Server backs up the transaction log, it also clears the portion of the log for reuse (assuming nothing else also needs the log). In order to do that, SQL Server implicitly CHECKPOINTs the database to push dirty pages to disk, […]