(New for 2020: we’ve published a range of SQL Server interview candidate screening assessments with our partner Kandio, so you can avoid hiring an ‘expert’ who ends up causing problems. Check them out here.)
(Check out my Pluralsight online training course: SQL Server: Logging, Recovery, and the Transaction Log.)
Here’s an interesting situation that sometimes crops up: you’re performing log backups, your transaction log isn’t growing, and yet the value of log_reuse_wait_desc in sys.databases for your database says LOG_BACKUP.
What’s going on?
I was actually just teaching this stuff today here in our last class of the year in Chicago.
Firstly, what is log_reuse_wait_desc? It’s a field in sys.databases that you can use to determine why the transaction log isn’t clearing (a.k.a truncating) correctly. Usually you query it when a log file is growing and you want to know why, using code like:
SELECT [log_reuse_wait_desc] FROM [master].[sys].[databases] WHERE [name] = N'Company'; GO
You can read about all the possible values in the Books Online topic Factors That Can Delay Log Truncation.
One important thing to bear in mind is that the value returned is the reason that log clearing did not clear any VLFs (Virtual Log Files – see Understanding Logging and Recovery in SQL Server) that last time that log clearing was attempted. For instance, you may see a value of ACTIVE_BACKUP_OR_RESTORE but you know that your full backup has completed. This means that the last time log clearing was attempted, the backup was still running.
Back to the original question. 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.
How can that happen?
Imagine a database where there’s very little insert/update/delete/DDL activity, so in between your regular log backups there are only a few log records generated, and they’re all in the same VLF. The next log backup runs, backing up those few log records, but it can’t clear the current VLF, so can’t clear log_reuse_wait_desc. As soon as there are enough changes in the database that the current VLF fills up and the next VLF is activated, the next log backup should be able to clear the previous VLF and then the log_reuse_wait_desc will revert to NOTHING. Until the next log backup occurs and isn’t able to clear the current VLF, in which case it will go back to LOG_BACKUP again.
So LOG_BACKUP really means “either you need to take a log backup, or the log records that were backed up were all in the current VLF and so it could not be cleared.”
Hope this helps!
28 thoughts on “Why is log_reuse_wait_desc saying LOG_BACKUP after doing a log backup?”
Thank you for your Explanation :), this is one of the things that was left Unanswered in my mind.
Thanks. It’s really help me.
Paul,
Thanks for this info but I still unclear in my case. DB data file is 5 GB, TrLog size is 37 GB and available is 30 GB, means 7 GB is still unavailable. Log_reuse_wait_desc is Log_backup but TrLog backup is running every 1 h and because of size (7 GB) all Log records probably cannot be “all in the current VLF”. What in that case keeping TrLog so big (I mean 7 GB)?
Thank, Yuri
The only way to reduce the size of the log is to shrink it. Simply backing it up only allows it to clear, which doesn’t change its size.
I don’t think that answers the (now aging) question above — he’s not asking about the size of the file, he’s asking about the 7GB of used space in the file. Could they really all be “in the current VLF”? How would one actually trigger that huge VLF to clear?
I’m dealing with a log that is roughly 400gb in size (which I want), but only 80gb is free. Even if the 320GB in use is in a single VLF, how would I get the system to “fill up” that vlf, considering it’s already so large?
You can’t. You’d need to generate enough log records for the current write point to be in a different VLF, and nothing requiring any log records in the large VLF you want to clear. But it doesn’t matter – as long as the log isn’t having to grow, why do you care about how much space is available? The natural cycle of writing more log records and regular log backups will ensure that log clearing happens regularly.
Thanks Paul — in my case, I needed more than 80gb to do some online index rebuilding, and I didn’t want the LDB to grow larger if at all possible.
I suspect that my issue was a rebuild in process, maybe preventing the truncation, as I’ve now got much more free space with which to rebuild my monster indexes!
Hey Paul, what about the log_reuse_wait_desc returns nothing. The database is only 6 gigs and log is 8 gigs and it would not shrink or backup log doesn’t reduced actual used size at all. Thanks!
How many VLFs does the log have? What version?
Thanks! Your explanation is clear and it’s really helping me out since I’ve been a DBA for only two months! :-)
Thanks! perfect explanation!
I had a database with log_reuse_wait_desc=’LOG_BACKUP’.
The database was in SIMPLE recovery model.
The command “dbcc loginfo” shows all the VLFs with Status=2 which means in use.
The command “dbcc opentran (‘{databasename}’) did not show any active transactions.
I knew the database was not involved in Replication
so I did not run ‘sp_removeDBreplication’
Since the database had little normal activity,
I tried ‘growing’ the database by doing
;insert into dummy_table select * from large_table
; drop table dummy_table
and tried to DBCC SHRINKFILE the Tlog
… all that happened is the Tlog just grew!
Finally we “solved” (worked around) the problem like this:
* detached the database
* re-attached the database.
Now the database showed log_reuse_wait_desc=’NOTHING’.
Now the DBCC SHRINKFILE worked.
My Sql Server version info:
Microsoft SQL Server 2012 (SP1) – 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)
Obviously Sql was “stuck” in a strange state on this database
and the detach/attach cleared the issue.
Interesting – was the database in either the full or bulk_logged recovery models previously?
MS KB article on this problem (if model is in SIMPLE) – https://support.microsoft.com/en-us/kb/2830400 – fixed in SQL2012CU7 / SQL2012SP1CU4.
Hi Paul – 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. Is my primary 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…
No – the primary cannot allow a VLF to clear on the primary if there are still log records from that VLF that need to be replayed on any secondary, regardless of availability mode.
Recovery Model of a database is Full and log_reuse_wait_desc = NOTHING.
after taking The First Log backup Log Space Used = 5 % But Shrink Log File IS not work and The size of the log file is not diminished.
But After Taking The Second Log Backup Shrink Log File IS Work and The size of the log file is diminished.
What is the cause of this?
First off, why are you trying to shrink the log? It could be because the first time you only had 2 or 3 VLFs. Hard to say without more information at the time.
Thanks For Your Answer!
because log file grows abnormally and The number of VLFs is more than fifty.
What is the best way to shrink the log file when the database recovery Model is full?
Well, it depends on the size. 50 VLFs is not a problem in itself. Shrinking can only be done using a shrink command, as long as the log can be cleared. I think I know what your original problem was. Even though only one VLF was in use, it was at the end of the log file. Shrink will stop when it reaches an active VLF. The next log backup will kick the active VLF round to the start of the log file, allowing the shrink to run. You might need to backup-shrink several times to make a shrink get to where you want it.
Thank you very much ! Mr. Paul Randal
Hi Paul, not quite on this topic but I couldn’t find a nearer article. I had a runaway log file grow to 320Gb. Root cause was a 3rd party tool timing out on truncating the log (and therefore failing to truncate). Resolved this, performed full and log backups successfully and found internal utilisation of the file was about 60GB. In order to reduce the space used on disk I went through SSMS > Databases > my database > Tasks > Shrink > Files, chose ‘Log’ file and chose the option where I could set a target size. I specified 100GB and hit ‘go’! After just a few short seconds the task completed. The file size reduced to 290GB and internal utilisation was still about 60GB. Thinking I’d somehow interrupted the task I ran it again with no further file size reduction. Why would it have stopped at that point? What’s keeping it from shrinking to 100GB on disk? Database is in full recovery mode. Many thanks for your informative articles, and also video training modules (elsewhere).
Hey Chris – log shrinking will always stop when it reaches the highest in-use VLF. You can see these using DBCC LOGINFO. You may need to backup-shrink-backup-shrink a few times to get it to truncate down further. Normal behavior as log shrinking won’t ever move anything. Thanks
Thank you for the reminder on dbcc loginfo. I ran that and it retrieved 174 VLFs. Ran the task to shrink the log file to 100GB again and this time the file on disk did reduce to 100GB. Ran dbcc and it subsequently contains 155 VLFs. As a matter of curiosity I found all the dropped VLFs came from the set having the largest FileSize value. There were 21 VLFs in this set and 19 removed. On sorting by StartOffset it shows the VLFs that were dropped were from the end of the file. I think it’s just coincidence that two were left after achieving the 100GB target. The last happens to be inactive while the one prior is active. I gather the shrink process drops VLFs off the end of the file so long as they are not active. If it hits an active one, shrink stops. Later, via normal logging activity, the active portions of the log may have shifted elsewhere*, leaving the latter VLFs inactive and free for removal via shrink. (*meaning portions that were active may be set inactive and vice versa – no physical moving of data).
In our environment we have 4 Node Replica 2 nodes in Synchronous Mode and 2 Nodes are in Asynchronous mode. Log back ups are happening every 15 minutes. The application just went live yesterday and one thing does not look right is that the data file used is only 800 mb where as the log file used is showing as 2.2 gb. My point is aren’t the log back up supposed to truncate the log file and make it clear for reuse.
There is no open transaction , the log_reuse_wait_desc shows as ‘NOTHING’ but the DBCC LOGINFO showing couple of VLFs with a status of 2. Even if i take log back up still stays at 2. Is this a normal behavior or after some time I will have to shrink the log files. Any workaround will be greatly appreciated.
By design – see https://www.sqlskills.com/blogs/paul/lazy-log-truncation-or-why-vlfs-might-stay-at-status-2-after-log-clearing/
Hi Paul, i am newer in MSSQL, i saw an article said if the value ‘log_reuse_wait_desc’ of a database does not equal to ‘nothing’, that cannot be shrink,
in my case is my log file is very large around 400GB, but when I check the the value ‘log_reuse_wait_desc’ of that database, it returned ‘log backup’, so my question is can a database be shirked when its ‘log_reuse_wait_desc’ is ‘log backup’?
No – you need to perform a log backup first.