Is Transaction Log Filling up Because Log Truncation is Delayed?

The volume that holds my transaction log seems to be constantly at 97% full, so I am trying to identify the cause.

DBCC SQLPERF(logspace)

Yields the following results:

There don’t seem to be any old transactions that are holding up the transaction log truncation:

SELECT
    database_transaction_begin_time,
    database_transaction_state,
    database_transaction_begin_lsn
FROM sys.dm_tran_database_transactions;

I suspect SQL Server is unable to truncate the transaction log because it cannot take a backup.
I run the following query on my database:

SELECT
    database_id,
    recovery_model_desc,
    log_reuse_wait,
    log_reuse_wait_desc
FROM sys.databases;

The result says that “model” (database_id=3) has log_reuse_wait_desc=LOG_BACKUP and recovery_model_desc=FULL.
This tells me that a log backup is required before the transaction log can be truncated (https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-2017#Truncation).

Next, I run this query to determine when the last log backup was taken (see https://stackoverflow.com/questions/40050221/sql-server-what-exactly-is-the-backup-finish-date-in-master-sys-databases):

SELECT
    d.dbid,
    b.type,
    MAX(b.backup_finish_date) AS backup_finish_date
FROM master.sys.sysdatabases d
    LEFT OUTER JOIN msdb..backupset b
         ON b.database_name = d.name
            AND b.type = 'L'
GROUP BY d.dbid, b.type
ORDER BY backup_finish_date DESC;

backup_finish_date=null, which tells me that model has never had a log backup.

These queries lead me to conclude

1. The transaction log cannot be truncated because a backup needs to be taken of “model”.
2. A backup of “model” has never been taken.

Am I interpreting these results correctly?

I am confused because “model” is a System database, and it doesn’t seem like I should have get involved with its log backups or truncation.

Can anyone recommend a way to free up space in the transaction log?

Am I interpreting these results correctly?

Yes, you are. Although it’s a system database, it doesn’t automatically back itself up. You must add these to your backup schedules just like the user databases.

Can anyone recommend a way to free up space in the transaction log?

As you read, and understood, back it up. Once you back up the TLOG (which will require a FULL BACKUP first if you haven’t done that) the % used will drop a lot. Then you can shrink the file. Go ahead and schedule these, or set the DB to SIMPLE MODE if you’re good with that.

I should mention none of your log files are that big. They also probably aren’t set to a max growth so they probably won’t fill up until your volume does. Be sure to check them.

The volume that holds my transaction log seems to be constantly at 97%
full, so I am trying to identify the cause.

I don’t see any database with the log full at 97% at all:

The max fullness of tran log file of your user databases is 8%,
the max among system databases is 61% for tempdb that is in simple mode and you cannot perform log backups of tempdb anyway.

Your second affermation is also wrong:

I suspect SQL Server is unable to truncate the transaction log because
it cannot take a backup.

Your server is able to truncate the log, it’s able to take log backups, and it effectively did both the things, it’s clear from your last picture where the log backups show recent data.

I really don’t understand what your problem is, everything regarding yor log files seems to be regular.

I think that the problem here is that Log truncation (the kind that happens when you backup up a log file) frees space in the log file for reuse by the transaction log, it does not actually shrink the log file on disk.

If that’s what you want you need to shrink the log file yourself. Unfortunately the command includes the word truncate, so one would expect that log file truncation and dbcc shrinkfile (truncateonly) were related, they are not.

There seems to be a database with a log file of almost 70GB in your list ,the fifth one. That seems like a good candidate to shrink. Let’s assume that it’s called WideWorldImporters:

use [WideWorldImporters]
GO
SELECT file_id, name, type_desc
FROM sys.database_files;
GO

This will tell us that:

Then just shrink it:

DBCC SHRINKFILE (2, TRUNCATEONLY); --file #2 is our log file