This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(89.60000000000001, 83%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ET2%3C/text%3E%3C/svg%3E)
I have a DB in full recovery mode on SQL Server 2016.
Until recently, I have been able to perform transaction log backup and the used space in the log file would be recovered and could be reused by new transactions.
Right now, while I run regular full backup and transaction log backup, the used space is not recovered.
I noticed that, even right after a tran log backup, the number of active VLF does not change and remains equal to the total VLF count. There are 0 inactive VLF.
Subsequent tran log backups are tiny, which tells me that the transactions are committed to the DB.
Why is the used space in the tran log not released and why do active VLF remain equal to total VLF?
Can the amount of available space in the database contribute to this condition?
I have been searching around, but not able to find how to tackle this condition.
If I would make a guess out of the blue, there is a transaction that has been left open and uncommitted. The transaction cannot be truncated past the oldest active transaction. You can check for this condition by running DBCC OPENTRAN in the database in question.
But there are other possible reasons. The more general solution is to run
SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'YourDB'
and then take it from there.
Hi Erland
Thanks alot for the suggestion.
I did run DBCC OPENTRAN earlier, which returned nothing.
I'll believe the result of log_reuse_wait_desc was NOTHING, but I will need to check this again tomorrow when I can access the system again.
Is there anything else which can cause this condition?
Underlying HW has no issues and I see no errors anywhere. Backups succeed every time.
I plan to restore the DB to a staging server and see if I can reproduce the issue there.
Could any DB settings, besides what you already mentioned, cause this condition?
I can mention that we ran this DB with an in-memory OLTP a few weeks ago, but this was removed.
The documentation for sys.databases lists these possible values:
NOTHING
CHECKPOINT
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
AVAILABILITY_REPLICA
OLDEST_PAGE
XTP_CHECKPOINT
SLOG_SCAN
Beware that once you have added an In-memory filegroup to a database, you cannot get rid of it, even if you drop all memory-optimised tables.
Hmm. Interesting.
I don't see the in-memory files on the database anymore, meaning all i see is the DB file and the Log file. It was my developers who initially implemented this and later removed it.
Result of "SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'YourDB'" showed LOG_BACKUP initially.
So I took a log backup which changed this to NOTHING.
But Active VLF is still equal to VLF Count and there are no Inactive VLF.
The used space in the log remains unchanged.
DBCC OPENTRAN show no transactions.
I am a bit lost on how to possibly resolve this issue.
Can I somehow create a new log file and drop/delete the current tran log?
So what query do you use to conclude that there are no inactive VLFs? If the log does not grow, that suggests that everything is fine.
You can add a second log file and when all of the active portion of the log is in the second file, you may be able to drop the first, but I would not recommend that you try that.
I found this script in a forum
SELECT [name] AS 'Database Name',
COUNT(l.database_id) AS 'VLF Count',
SUM(CAST(vlf_active AS INT)) AS 'Active VLF',
COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'Inactive VLF',
SUM(vlf_size_mb) AS 'VLF Size (MB)',
SUM(vlf_activevlf_size_mb) AS 'Active VLF Size (MB)',
SUM(vlf_size_mb)-SUM(vlf_activevlf_size_mb) AS 'Inactive VLF Size (MB)'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name]
ORDER BY COUNT(l.database_id)
When I run it on my SQL server, it list down the following for this specific database
VLF count: 302
Active VLF: 302
Inactive VLF: 0
VLF Size (MB) 52300
Active VLF Size (MB): 52300
Inactive VLF Size (MB): 0
I have a second DB on that same server, which is a tiny DB.
This DB has a VLF count of 24, where 1 is active and 23 are inactive.
Total VLF size is only 10MB. This DB has very little activity.
Issue I have is that the transaction log space is large, much larger than it needs to be and I have challenges managing it. I know 50GB does not sound like much these days, but for this server, it is causing challenges.
While the log space will likely not grow much or anything beyond this point, I want it significantly reduced, maybe to around 10GB.
I took a backup of this database and restored it to a development server I have, but facing the same issues there. Not able to truncate the log space.
I even changed the DB to SIMPLE recovery mode, but still not able to do anything about that log file.
Not sure what else I can try here...
So there is a way to throw the current transaction log away, but it is not a step to take lightly. This will obviously break the log chain, so before this you do this, you should run DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS to make as sure as possible that you don't have any corruption. I would also recommend that you run this on a restored backup to be more certain.
The way you do it is that you create a database snapshot. You can do some operations in the source database, but nothing you want to keep. To wit, next step is to revert from the snapshot. You do this with RESTORE DATABASE, saying FROM DATABASE_SNAPSHOT rather than FROM DISK. When you restore the snapshot, the log will be thrown away, and you will get a transaction log of half a megabyte. Which you obviously you should grow to the desired size immediately.
I would strongly recommend that you first try this operation in your dev environment.
Thank you very much Erland
I will definitely test this procedure out in my dev environment and see if this could help to fix the issue.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 2%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESX%3C/text%3E%3C/svg%3E)
Hi @Thomas 2022 ,
Welcome to Microsoft Q&A!
In addition, you need to set up the automatic growth. You can set it to grow by a fixed size. Depending on your situation, you can set it to a larger value. Try a few more log backups to see if the state of the VLF changes.
If more inactive VLFs are found after multiple backups, you can shrink it after the backup.
Best regards,
Seeya
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hi Seeya
Thank you for the suggestion. Auto-growth is in place.
We have performed many tran log backups, but the number of Active VLF remains equal to Total VLF with zero Inactive VLF.