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

by

Troubleshooting Guide: SQL Server Error 2504 – “Could Not Delete the Physical File”

Why This Error Happens

This error occurs when the SQL Server service account lacks the necessary NTFS permissions to delete the specified database file, or when another process—such as an antivirus scanner, backup agent, or a lingering SQL Server connection—has locked the file, preventing the DeleteFile Windows API call from succeeding. It can also happen if the file path is incorrect, the file was already removed outside of SQL Server, or the underlying disk/volume has become read-only or disconnected due to storage subsystem issues.

Step-by-Step Resolution Guide

Step 1: Verify File Permissions and Ownership

  • Navigate to the data directory (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) and right-click the .mdf/.ldf/.ndf file in question.
  • Go to Properties > Security and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
  • If missing, click Edit, add the service account, and grant Full Control, then click Apply.

Step 2: Check for File Locks from External Processes

  • Open Resource Monitor (resmon.exe) and go to the CPU tab, then search in Associated Handles using the file name.
  • Identify any process (antivirus, backup software, Windows Search Indexer) holding a handle to the file and terminate or exclude it.
  • Add an antivirus exclusion for the SQL Server DATA/LOG directories via Windows Security or your third-party AV console.

Step 3: Confirm No Active SQL Server Connections Exist

  • Run the following T-SQL to check for active sessions on the database:
SELECT session_id, status, host_name FROM sys.dm_exec_sessions WHERE database_id = DB_ID('YourDBName');
  • If connections exist, set the database to single-user mode before retrying the delete:
ALTER DATABASE YourDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Step 4: Validate the Storage Volume Status

  • Open Disk Management (diskmgmt.msc) and confirm the volume hosting the data file is Online and not set to Read-Only.
  • Run chkdsk C: /f (replace C: with the correct drive letter) from an elevated Command Prompt to repair any underlying file system corruption.
  • Reboot the server if a volume lock is detected, then retry the file deletion through SQL Server Management Studio (SSMS).

Step 5: Manually Remove the File via SQL Server Command

  • If the file reference still exists in the database metadata, use ALTER DATABASE ... REMOVE FILE after resolving locks:
ALTER DATABASE YourDBName REMOVE FILE LogicalFileName;
  • If SQL Server still can’t delete the physical file, manually delete it from File Explorer once SQL Server services are stopped, then restart the SQL Server service.

Alternative or Verification Steps

  • Verify the Fix: Re-run the original operation (e.g., DROP DATABASE, file removal, or shrink operation) and confirm Error 2504 no longer appears in the SQL Server Error Log or Windows Event Viewer (Application log).
  • Alternative Solution: If the file is permanently inaccessible due to a corrupted volume, take the database offline, restore from the latest backup to a healthy volume, and then decommission the original damaged file path.