Sql-server – Is Transaction Log Filling up Because Log Truncation is Delayed

sql server

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:

SSMS SQLPERF screenshot

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;

SSMS dm_tran_database_transactions screenshot

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;

SSMS sys.databases screenshot

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;

SSMS sys.databases screenshot

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?

Best Answer

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.

Related Question