0

I have a database myDB in full recovery model. I took a full backup and restored it on the test server. Afterwards, I checked the size of T-log. The size of t-log was 15600 MB and used space out of total space was 351 MB. I decided to truncate those 351 MB and ran T-log backup. Unfortunately, the size does not change at all. It again showed 351 MB. Then I took other subsequent t-log backups. The size still 351. Why t-log backup does not truncate log file? I am not asking about shrinking, I just want to know why t-log does not truncate t-log.

Here are some details regarding t-log:

From DBCC LOGINFO

enter image description here

From querying log_reuse_wait_desc:

enter image description here

About the size of T-log:

enter image description here

2

The 8th row in your first screenshot shows what appears to be an active VLF in the file. If there is a single active log record in a VLF then the overall VLF will be treated as active log and that VLF cannot be truncated.

If the server is SQL 2016 SP2 or later, you can use the below DMF to get information on the VLFs and check the active state and size. You will likely find that the active VLF is around 350 MB, and if even one byte is active, the entire VLF is considered active and backing up the log won't mark it for truncation.

SELECT s.[name], l.vlf_size_mb, l.vlf_active
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l

Further reading:

What are virtual log files in a SQL Server transaction log?

SQL Server Transaction Log Backup, Truncate and Shrink Operations

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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