The volume that holds my transaction log seems to be constantly at 97% full, so I am trying to identify the cause.
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
- The transaction log cannot be truncated because a backup needs to be taken of "model".
- 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?