DBA Scripts: Get Transaction Log Size and Usage

---

Understanding Transaction Log Size Usage

Keeping an eye on transaction log usage is one of the simplest ways to prevent unexpected database outages. When a transaction log fills, SQL Server cannot continue writing transactions, backups begin failing, applications stop processing writes, and production quickly turns into an incident.

Many environments monitor overall disk capacity but don’t monitor how much of each transaction log is actually being used. A 500 GB log file isn’t necessarily a problem if only 20 GB is in use, while a 20 GB log file that’s 99% full could stop an application within minutes.

This script provides a quick overview of every user database, showing the total log size, how much space is currently used, how much remains free, and the percentage of the transaction log currently in use. It gives DBAs an immediate health check without needing to inspect each database individually.

---

Why Transaction Log Size and Usage Matters

Transaction logs are vital for database recovery and point-in-time restore capabilities. However, if logs grow too large or are not managed properly, they can cause several operational issues:

• Disk space exhaustion leading to database downtime
• Increased backup and restore times
• Slower overall database performance
• Difficulties in managing database growth and capacity planning

Understanding the size, usage, autogrowth settings, and last backup times of transaction logs enables DBAs to maintain a healthy, efficient environment and avoid critical failures.

Every modification made to a SQL Server database is first written to the transaction log. SQL Server relies on the transaction log for:

• Transaction rollback
• Crash recovery
• Point-in-time restores
• Log shipping
• Always On Availability Groups
• Database mirroring
• Replication
• Change Data Capture (CDC)

As transactions are committed and log backups occur (for FULL or BULK_LOGGED recovery models), SQL Server marks portions of the log as reusable. The physical log file does not automatically shrink; it reuses available space inside the existing file.

---

Common Symptoms

• Frequent autogrowth events in the transaction log
• Log files occupying a significant portion of disk space unexpectedly
• Slow database recovery or backup times
• Errors related to insufficient disk space during transaction log operations
• Transaction log backups failing due to insufficient disk space
• Transaction log full errors (9002)
• Failed INSERT, UPDATE, or DELETE operations
• Log backups becoming unusually large
• Always On secondary replica lag
• Long running transactions preventing log truncation

---

When to Run This Script

• During routine health checks of SQL Server instances
• After large transaction volumes or significant database activity
• When experiencing disk space issues or log growth anomalies
• Before scheduled maintenance windows to assess current log sizes
• Following log backup failures or delays

---

The Script

Run the following script against your SQL Server instance to get detailed insights into transaction log sizes and usage:

/*
Script Name : Get-TransactionLogSizeAndUsage
Category    : storage-capacity-management
Purpose     : Show transaction log size, used space, free space, and percent used per database.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-transaction-log-size-and-usage/)
Requires    : VIEW SERVER STATE (DBCC SQLPERF), read on msdb.dbo.backupset
Notes       : Usage figures come from DBCC SQLPERF(LOGSPACE), which reports every database.
              FILEPROPERTY is current-database-scoped and silently returned 0 for all other
              databases (fixed 2026-07-19). Offline databases show NULL usage.
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

DECLARE @logspace TABLE
(
    database_name sysname NOT NULL PRIMARY KEY,
    log_size_mb FLOAT NOT NULL,
    log_space_used_pct FLOAT NOT NULL,
    status INT NOT NULL
);

INSERT INTO @logspace (database_name, log_size_mb, log_space_used_pct, status)
EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS');

;WITH LogBackup AS
(
    SELECT
        database_name,
        MAX(backup_finish_date) AS last_log_backup
    FROM msdb.dbo.backupset
    WHERE type = 'L'
    GROUP BY database_name
)

SELECT
    d.name AS database_name,

    d.state_desc,

    d.recovery_model_desc,

    d.log_reuse_wait_desc,

    COUNT(mf.file_id) AS log_file_count,

    STRING_AGG(mf.physical_name, '; ') AS log_file_paths,

    CAST(
        SUM(CAST(mf.size AS BIGINT)) * 8.0 / 1024
        AS DECIMAL(18,1)
    ) AS log_size_mb,

    CAST(
        MAX(ls.log_size_mb) * MAX(ls.log_space_used_pct) / 100.0
        AS DECIMAL(18,1)
    ) AS log_used_mb,

    CAST(
        MAX(ls.log_size_mb) * (100.0 - MAX(ls.log_space_used_pct)) / 100.0
        AS DECIMAL(18,1)
    ) AS log_free_mb,

    CAST(
        MAX(ls.log_space_used_pct)
        AS DECIMAL(5,1)
    ) AS log_used_pct,

    CASE
        WHEN COUNT(DISTINCT mf.is_percent_growth) > 1
            THEN 'Mixed'

        WHEN MAX(CAST(mf.is_percent_growth AS INT)) = 1
            THEN CAST(MAX(mf.growth) AS VARCHAR(20)) + '%'

        ELSE
            CAST(
                (MAX(CAST(mf.growth AS BIGINT)) * 8) / 1024
                AS VARCHAR(20)
            ) + ' MB'
    END AS autogrowth_setting,

    CASE
        WHEN COUNT(DISTINCT mf.is_percent_growth) > 1
            THEN 'Mixed'

        WHEN MAX(CAST(mf.is_percent_growth AS INT)) = 1
            THEN 'Percent'

        ELSE
            'Fixed MB'
    END AS autogrowth_type,

    CASE
        WHEN MAX(mf.max_size) = -1
            THEN 'Unlimited'

        ELSE
            CAST(
                (MAX(CAST(mf.max_size AS BIGINT)) * 8) / 1024
                AS VARCHAR(20)
            ) + ' MB'
    END AS max_size,

    lb.last_log_backup,

    CASE
        WHEN lb.last_log_backup IS NULL
            THEN NULL

        ELSE
            DATEDIFF(
                MINUTE,
                lb.last_log_backup,
                GETDATE()
            )
    END AS minutes_since_last_log_backup
FROM sys.master_files AS mf
JOIN sys.databases AS d
    ON mf.database_id = d.database_id
LEFT JOIN @logspace AS ls
    ON ls.database_name = d.name
LEFT JOIN LogBackup AS lb
    ON d.name = lb.database_name
WHERE mf.type_desc = 'LOG'
AND d.database_id > 4
GROUP BY
    d.name,
    d.state_desc,
    d.recovery_model_desc,
    d.log_reuse_wait_desc,
    lb.last_log_backup
ORDER BY
    log_used_pct DESC,
    log_size_mb DESC;

---

How To Run From The Repo

Clone the DBA Tools repository, initialize your environment, and execute the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Run the transaction log size and usage check:
.\run.ps1 Get-TransactionLogSizeAndUsage

# To run against a remote SQL Server instance:
.\run.ps1 Get-TransactionLogSizeAndUsage -ServerInstance SQLSERVER01

This will execute the script and generate a detailed report on your transaction logs, helping you stay on top of disk space and log management.

---

Example Output

Example output from the DBA Scripts Get Transaction Log Size and Usage script in the Web-UI; showing transaction log health metrics across SQL Server databases including size, utilisation percentage, autogrowth configuration, and backup history.

database_name      state_desc recovery_model_desc log_file_count log_size_mb log_used_mb log_free_mb log_used_pct autogrowth_setting autogrowth_type max_size last_log_backup minutes_since_last_log_backup

WatchtowerMetrics  ONLINE     SIMPLE              1              1322.2      1322.2      0.0         100.0        64 MB              Fixed MB         2097152 MB **2026-07-10 20:38:25** 226235

GrowthLab          ONLINE     FULL                1              648.0       648.0       0.0         100.0        64 MB              Fixed MB         2097152 MB NULL NULL

DemoDatabase       ONLINE     FULL                1              512.0       256.0       256.0       50.0         256 MB             Fixed MB         2097152 MB NULL NULL

---

Understanding the Results

The report provides essential metrics: total log size, used and free space, percentage used, autogrowth settings, number of log files, and time since the last log backup.

• High log_used_pct (especially near 100%) indicates the transaction log currently has a high percentage of allocated space containing active or retained log records. Persistent high usage, especially when combined with delayed log backups or long running transactions, can lead to unexpected growth and outages.
• Long minutes_since_last_log_backup in FULL recovery model is a red flag for potential log growth issues. This does not apply to databases intentionally operating without log backups, such as databases using SIMPLE recovery model.
• Autogrowth settings help identify risky configurations (e.g., very small fixed increments or percent growth on large logs).

Regularly review these metrics to ensure logs are managed efficiently, preventing disk space issues and maintaining database performance.

---

Common Causes

• Infrequent log backups leading to unbounded log growth
• Long-running transactions preventing log truncation
• Misconfigured autogrowth settings (too small or percent-based on large files)
• Large transaction volumes or bulk operations
• Replication backlog or CDC processing delays
• Insufficient disk space for log files

---

How to Fix Problems

The correct fix depends on why the log cannot be reused.

Common actions include:

• Schedule regular log backups (especially in FULL recovery model) to truncate inactive portions
• Adjust autogrowth settings based on workload and database growth patterns. Avoid very small increments and percentage based growth on large transaction logs
• Monitor and optimize or kill long-running transactions
• Allow replication or Always On replicas to catch up
• Consider switching to SIMPLE recovery model for non-critical databases (if point-in-time recovery is not required)
• Increase disk capacity or move log files to larger drives if necessary

---

Best Practices

• Automate regular log backups and maintenance tasks
• Monitor log size trends over time (consider pairing with growth history scripts)
• Set autogrowth increments to balance between frequent autogrowth events and disk space usage
• Regularly review database recovery models and autogrowth settings
• Document and review log management policies
• Alert on logs > 80% full or backups older than X hours
• Trend transaction log utilisation over time rather than relying on a single reading

---

Related Scripts

• Storage and Capacity (hub)
• Autogrowth History
• Database Files Detail
• Database Free Space Summary
• Database Growth Risk and Forecast
• Database Sizes and Free Space
• Disk Space
• Filegroup Space
• VLF Counts
• DBA Scripts: The Complete Guide, the map across every script on this site

---

FAQs

Q: What is Transaction Log Size and Usage?
A: It indicates how much space the transaction log occupies, how much is used, free space, and the percentage used, helping DBAs monitor log health and growth.

Q: How often should I check transaction log sizes?
A: As part of regular maintenance — daily for critical systems, weekly for others — especially after heavy transactions or if disk space becomes constrained.

Q: Why does my log show 100% used even after a backup?
A: In FULL recovery, a log backup truncates the log but does not shrink the file. The physical log file size remains unchanged unless it is manually shrunk. Use DBCC SHRINKFILE sparingly if needed, or grow the file proactively.

---

Summary

Effective management of transaction logs is vital for maintaining SQL Server health and performance. Regularly monitoring log sizes, usage, and backup status helps prevent unexpected disk space issues and ensures smooth recovery operations. Incorporate this script into your routine health checks to stay ahead of potential storage problems and optimize your database environment for stability and growth.