How to fix Error 5039 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 5039 – MODIFY FILE Failed in SQL Server 2025

Why This Error Happens

Error 5039 occurs when an ALTER DATABASE ... MODIFY FILE statement attempts to set a data or log file’s size to a value that is smaller than or equal to its current allocated size. SQL Server’s MODIFY FILE command is designed exclusively for growing files or changing file properties (like FILEGROWTH or file path on restart); it cannot be used to shrink a file. This typically happens when an administrator miscalculates the current file size in MB/GB or confuses the MODIFY FILE command with the DBCC SHRINKFILE command, which is the correct tool for reducing file size.

Step-by-Step Resolution Guide

Step 1: Verify the Current File Size

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the target instance.
  • Run the following query to check the exact current size of the file in megabytes (SQL Server stores size in 8-KB pages internally):
SELECT name AS FileName, size/128.0 AS CurrentSizeMB 
FROM sys.database_files;
  • Compare this output to the value you attempted to use in your MODIFY FILE statement to confirm the conflict.

Step 2: Correct the T-SQL Syntax for Growth

  • If your intention was to increase the size, ensure the new size value is strictly greater than the current size found in Step 1.
  • Execute the corrected command with a valid incremental value:
ALTER DATABASE [YourDatabaseName] 
MODIFY FILE (NAME = N'YourLogicalFileName', SIZE = 20480MB);
  • Ensure there is sufficient disk space on the Windows Server 2025 volume (e.g., D:\SQLData) to accommodate the new size before executing.

Step 3: Use DBCC SHRINKFILE for Reductions

  • If the actual goal was to reduce the file size, do not use MODIFY FILE. Instead, use the DBCC SHRINKFILE command.
  • Run the following command to shrink the file to a specific target size in MB:
USE [YourDatabaseName];
GO
DBCC SHRINKFILE (N'YourLogicalFileName', 15000);
  • Be cautious of fragmentation; shrinking files is generally discouraged in production environments as it can severely impact performance.

Step 4: Check for Instant File Initialization (IFI) Conflicts

  • In rare cases on Windows Server 2025, discrepancies in “size on disk” vs. “logical size” reporting can occur if the Perform Volume Maintenance Tasks policy is misconfigured.
  • Verify the SQL Server service account has this permission via secpol.msc under Local Policies > User Rights Assignment to ensure file operations report accurate size metadata to the Database Engine.

Alternative or Verification Steps

  • Verify the Fix: Re-run the sys.database_files query from Step 1 after executing your command to confirm the size column reflects the new, larger value you intended to set.
  • Alternative: If you need to migrate data to a smaller file structure entirely (rather than shrinking), create a new filegroup, move the data via CREATE INDEX ... WITH (DROP_EXISTING = ON) on the new filegroup, and then remove the old, oversized file once it is empty.