Sql-server – Why T-log is not truncated after T-log backup

backuprecovery-modelsql servertransaction-log

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

From querying log_reuse_wait_desc:

About the size of T-log:

Best Answer

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

Related Solutions

Sql-server – Shrinking a SQL Server Log File Multiple Times

Next time, run two checkpoints before trying to shrink the file (assuming simple recovery) or take two log backups (assuming full or bulk-logged). This will force the log to wrap around to the beginning of the file, allowing the shrinkfile operation to eradicate most of the file. I think the fact that the 4th operation was successful was just by coincidence (perhaps multiple checkpoints happened in the meantime).

That said, why on earth would you want a 1 MB log file? If it grew once, it will grow again. Freeing up the space temporarily just so that it can autogrow during real activity (which blocks, because the growth of the log has to be zero-initialized, even with instant file initialization) seems like an exercise in futility. Why not just size the log appropriately, so that you don't have to deal with growths (ideally) or shrinks (wasteful).

Please have a good thorough read of this question and its answers:

Why Does the Transaction Log Keep Growing or Run Out of Space?

Further reading that might be more useful than trying to summarize:

Sql-server – Unable to truncate transaction log, log_reuse_wait_desc – AVAILABILITY_REPLICA

If you do this:

SELECT * FROM sys.databases

And the log_reuse_wait_desc shows AVAILABILITY_REPLICA, that means SQL Server is waiting to send log data to one of your Always On Availability Group replicas. One of the replicas may be lagging behind due to a slow network, or it may be down altogether.

If you check the AG dashboard and it shows no queues, you may have been a victim of thread exhaustion. It's a known issue that the AG dashboard stops updating after worker thread exhaustion. You'll need to check the status on each replica directly rather than relying on the primary. Nick's note in that Connect item says that you can just alter a replica's properties to restart replication, but that doesn't always work (especially if you have hundreds of databases on a replica with a large amount of data that needs to be sent, and restarting replication can just cause the worker thread exhaustion again.)

If the last guy set up an AG replica and it's not supposed to exist anymore, then it's time to remove that AG and/or replica. Just be careful that apps aren't pointing to the listener name in order to connect to your SQL Server.

Best Answer

Related Solutions

Sql-server – Shrinking a SQL Server Log File Multiple Times

Sql-server – Unable to truncate transaction log, log_reuse_wait_desc – AVAILABILITY_REPLICA

Related Question