2

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?

  • 1
    You probably want to keep model in the mode you want for new databases, so don’t switch it to simple mode. You can backup it, but I suspect it won’t grow much as there are no writes typically. The other system databases are already in Simple backup mode, so you don’t need to worry about their logs. Generally speaking you don’t want to operate at 96% capacity level for your log disk (in specific cases with limited and controlled load you can, but then set at least a maximum size for the files. And i would not bother shrinking them.) – eckes Jan 5 at 16:39
3

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.

0

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:

enter image description here

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 said the volume (as in the OS's physical storage) that holds the transaction logs - not any one database log - is at 97% full. – eli Jan 7 at 16:58
  • The results from DBCC SQLPERF(logspace) do appear to indicate that a backup of "model" was taken at some point in time. Is this why you say "Your server is able to truncate the log, it's able to take log backups, and it effectively did both the things"? But this could be from a long time ago, it's not necessarily recent. And I'm confused because this seems to conflict with the results from sys.sysdatabases, which show NULL for backup_finish_date. – eli Jan 7 at 17:05
  • >>>OS's physical storage<<< So, you said, your disk that contains 5Gb of tran log files is ALMOST FULL? Sorry, even my laptop has 1Tb, do you really speak about PRODUCTION SERVER? – sepupic Jan 8 at 7:25
  • >>>The results from DBCC SQLPERF(logspace) do appear to indicate that a backup of "model" was taken at some point in time.<<< Not really. Model just has no transaction activity. And you've never backed it up. – sepupic Jan 8 at 7:29
0

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:

enter image description here

Then just shrink it:

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

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.