Troubleshooting Guide: SQL Server Error 5150 — “The size of a single log file must not be greater than 2 TB”

Why This Error Happens

This error occurs because SQL Server enforces a hard architectural limit of 2 TB on the maximum size of a single transaction log file (VLF container), regardless of available disk space. It is typically triggered when auto-growth settings, a manually specified file size, or FILEGROWTH/MAXSIZE configurations attempt to expand or create a log file (.ldf) beyond this ceiling. This commonly happens on databases with heavy transactional activity, unchecked log growth due to infrequent log backups, or during a restore/create operation where the size parameter was set incorrectly.

Step-by-Step Resolution Guide

Step 1: Verify the Current Log File Size and Database Settings

Open SQL Server Management Studio (SSMS) and connect to the SQL Server 2025 instance.
Run the following query to check current log file sizes and growth settings:

SELECT name, size/128.0 AS CurrentSizeMB, max_size, growth, is_percent_growth 
FROM sys.master_files 
WHERE database_id = DB_ID('YourDatabaseName');

Identify if max_size is set to -1 (unlimited) or if the current size is already approaching 2,097,152 MB (2 TB).

Step 2: Implement Log File Management (Instead of Infinite Growth)

Since a single log file cannot exceed 2 TB, you cannot simply “add more space” to the same file. Instead, break the log backup chain dependency by managing VLF growth.
Switch the database recovery model temporarily to Simple (if business logic allows) to truncate the log, or ensure transaction log backups are running frequently to prevent unchecked growth:

BACKUP LOG [YourDatabaseName] TO DISK = 'E:\Backups\YourDatabaseName_Log.trn';

Shrink the log file after backup to reclaim internal space without needing to exceed the 2 TB physical limit:

DBCC SHRINKFILE (YourDatabaseName_Log, 500000);

Step 3: Add a Secondary Log File (If Capacity is Still Required)

If the business requirement genuinely needs more than 2 TB of total log capacity, you cannot extend file #1; you must add a second log file to the same filegroup.
Navigate to the data drive path (e.g., F:\SQLData\Logs\) and ensure there is sufficient disk space for a new file.
Execute the following T-SQL to add a secondary log container:

ALTER DATABASE [YourDatabaseName] 
ADD LOG FILE 
(
    NAME = YourDatabaseName_Log2, 
    FILENAME = 'F:\SQLData\Logs\YourDatabaseName_Log2.ldf', 
    SIZE = 500GB, 
    FILEGROWTH = 500MB
);

Step 4: Audit and Correct Auto-Growth Settings

To prevent future triggers of Error 5150, change the auto-growth setting from a “percentage” growth to a “fixed MB” growth to avoid erratic spikes.
In SSMS, right-click the Database -> Properties -> Files -> Click the (…) button next to the Log file.
Set Autogrowth/Maxsize to a fixed value (e.g., Growth: 512MB, Maximum File Size: 1,900,000 MB) to leave a safety buffer below the 2 TB hard limit.

Alternative or Verification Steps

Verification: Re-run the sys.master_files query from Step 1. Confirm that max_size is now capped below the 2TB limit (2,097,152 MB) and that the state_desc for the file shows ONLINE.
Alternative: If the log continues to grow uncontrollably even after backups, check for orphaned replication or Change Data Capture (CDC) jobs that might be preventing the log from truncating, as these can cause rapid consumption toward the 2 TB ceiling even with regular backups.