Log Space Usage DMV: Check Transaction Log Space in T-SQL

The Log Space Usage DMV, sys.dm_db_log_space_usage, answers three questions about a transaction log with one row. How big is it? How full is it? How much log has piled up since the last log backup? The third answer decides when a log backup is worth taking.

What the View Returns

The Log Space Usage DMV reports on the current database, so run it after a USE statement. It has one row, and four columns carry the story.

The total log size is the size of the file. The used log space holds the log records SQL Server still needs. The view also shows it as a percent. The fourth column, log space since the last backup, counts the log written since the last log backup. That is roughly what the next log backup will contain.

All three size columns are in bytes. Divide by 1048576.0 to get megabytes. Name your output columns by their unit, because the raw values are bytes until you divide.

Build the Demo

The demo creates a database named LogSpaceDemo in the FULL recovery model, adds a table, and takes a full backup. The backup starts the log chain, and without it the since-last-backup number has nothing to measure from. Change the backup folder to one that exists on your test server.

IF DB_ID(N'LogSpaceDemo') IS NULL CREATE DATABASE LogSpaceDemo;
GO
ALTER DATABASE LogSpaceDemo SET RECOVERY FULL;
GO
USE LogSpaceDemo;
GO
DROP TABLE IF EXISTS dbo.Readings;
CREATE TABLE dbo.Readings (ReadingID int IDENTITY(1,1) PRIMARY KEY, Note char(200) NOT NULL DEFAULT 'x');
GO
BACKUP DATABASE LogSpaceDemo TO DISK = N'D:\data\LogSpaceDemo_full.bak' WITH INIT;
GO
INSERT INTO dbo.Readings (Note) SELECT TOP (30000) 'x' FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;

Read the Log Space

The query below converts each column to megabytes and rounds it.

SELECT CAST(total_log_size_in_bytes / 1048576.0 AS decimal(10,2)) AS TotalLogMB,
       CAST(used_log_space_in_bytes / 1048576.0 AS decimal(10,2)) AS UsedLogMB,
       CAST(used_log_space_in_percent AS decimal(5,1)) AS UsedPercent,
       CAST(log_space_in_bytes_since_last_backup / 1048576.0 AS decimal(10,2)) AS SinceLastBackupMB
FROM sys.dm_db_log_space_usage;

The insert wrote about 10 MB of log, and all of it is waiting for a backup. The file is 72 MB because the log grew while the insert ran. Your numbers will differ with your server settings, and a repeat on the same server stays within about 0.1 MB.

Back Up the Log Only When It Pays

A job that backs up the log every few minutes creates many tiny files. In a restore, every file has to be applied in order. A threshold avoids the tiny ones. The script reads the since-last-backup value and takes the backup only when it passes a limit. The demo uses 5 MB. Pick a limit that fits your restore plan.

DECLARE @ThresholdMB decimal(10,2) = 5.00;
IF (SELECT log_space_in_bytes_since_last_backup / 1048576.0 FROM sys.dm_db_log_space_usage) > @ThresholdMB
    BACKUP LOG LogSpaceDemo TO DISK = N'D:\data\LogSpaceDemo_log.trn' WITH INIT;
SELECT CAST(used_log_space_in_bytes / 1048576.0 AS decimal(10,2)) AS UsedLogMB,
       CAST(log_space_in_bytes_since_last_backup / 1048576.0 AS decimal(10,2)) AS SinceLastBackupMB
FROM sys.dm_db_log_space_usage;

The since-last-backup value dropped to almost nothing, and the used space fell with it. Used space doesn’t always fall that far. It counts the active part of the log, which depends on more than backups. A schedule that runs this check every few minutes keeps the backups worth restoring and still protects the data.

Uncommitted Work Counts Too

A common question is whether the since-last-backup number includes transactions that haven’t committed. It does. The next script opens a transaction, inserts 5,000 rows, and reads the view before it ends.

BEGIN TRANSACTION;
INSERT INTO dbo.Readings (Note) SELECT TOP (5000) 'y' FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
SELECT CAST(log_space_in_bytes_since_last_backup / 1048576.0 AS decimal(10,2)) AS InsideTransactionMB FROM sys.dm_db_log_space_usage;
ROLLBACK TRANSACTION;
SELECT CAST(log_space_in_bytes_since_last_backup / 1048576.0 AS decimal(10,2)) AS AfterRollbackMB FROM sys.dm_db_log_space_usage;

The value grew before the commit, and the rollback wrote more log. A log backup can’t free log that an open transaction still needs. A big load or an index rebuild can fill a log even when backups run on time.

The Simple Recovery Model

Simple recovery has no log backups, so the since-last-backup column means little there. The view still works, and the first three columns stay useful.

ALTER DATABASE LogSpaceDemo SET RECOVERY SIMPLE;
CHECKPOINT;
SELECT CAST(used_log_space_in_bytes / 1048576.0 AS decimal(10,2)) AS UsedLogMB,
       CAST(log_space_in_bytes_since_last_backup / 1048576.0 AS decimal(10,2)) AS SinceLastBackupMB
FROM sys.dm_db_log_space_usage;

Checking Every Database

The view reports on one database at a time. To survey the whole instance, run DBCC SQLPERF(LOGSPACE). It lists every database with its log size and the percent used. It has no since-last-backup column, so use it for the overview and the view for the decision.

The threshold check fits in a SQL Server Agent job step. Run it against each database that uses FULL recovery, every few minutes. The step takes a log backup only when the number says it’s worth one.

Is a Threshold Worth the Trouble?

You could argue that a fixed schedule is simpler. Back up the log every 15 minutes, and stop thinking about it. That works.

The threshold earns its place when the load swings. A quiet night produces empty files, and a busy hour produces a log that grows faster than the schedule. A check that watches the value covers both cases.

What to Remember

Read the Log Space Usage DMV before you decide on a log backup. Divide the bytes by 1048576.0 and label the columns correctly. Use the since-last-backup value to decide when a log backup is worth it. Remember that open transactions count and hold the log. A used percent that keeps climbing after each log backup is the sign to look for an open transaction. The column log_reuse_wait_desc in sys.databases confirms it, with a value such as ACTIVE_TRANSACTION or LOG_BACKUP. Remove the demo and its backup files when you finish.

USE master;
GO
ALTER DATABASE LogSpaceDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE LogSpaceDemo;
GO
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'LogSpaceDemo';

The last statement removes the backup history rows of the demo from msdb. The two backup files stay on disk. The next block is PowerShell, not T-SQL, and it deletes exactly those two files.

Remove-Item 'D:\data\LogSpaceDemo_full.bak', 'D:\data\LogSpaceDemo_log.trn'

A log is not a trash bin, it is a ledger the DMV reads for you.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.