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

by

Troubleshooting Guide: SQL Server Error 3738 — “Deleting database file ‘%ls'”

Why This Error Happens

Error 3738 is typically an informational message logged during a DBCC SHRINKFILE operation with the EMPTYFILE option, indicating SQL Server is in the process of removing an empty database file. However, when this message appears unexpectedly or the deletion stalls, it usually points to the file not being fully empty (residual data pages not migrated), the file being referenced by active transactions, or insufficient permissions on the underlying filesystem path preventing SQL Server’s service account from completing the physical file removal. In rarer cases, filesystem-level locks (antivirus, backup agents) or storage corruption on Windows Server 2025 volumes can prevent the final handle release needed to delete the file.

Step-by-Step Resolution Guide

Step 1: Confirm the File Is Actually Empty

  • Run DBCC SHRINKFILE (N'logical_file_name', EMPTYFILE); again to ensure all data pages were migrated out successfully.
  • Query sys.dm_db_file_space_usage to verify zero allocated extents remain in the target file.
  • If pages remain, check for LOB/text columns or indexes that didn’t shrink cleanly; rebuild indexes with ALTER INDEX ALL ON [table] REBUILD; first.

Step 2: Check for Active Transactions or Open Handles

  • Query sys.dm_tran_active_transactions and sys.dm_exec_sessions to confirm no open transactions are referencing the database.
  • Use sp_who2 or sys.dm_exec_requests to identify blocking sessions tied to the database ID.
  • On Windows Server 2025/Windows 11, use Resource Monitor (resmon.exe) or handle.exe (Sysinternals) to check if an external process (antivirus, backup software) has a lock on the .mdf/.ndf file.

Step 3: Verify Service Account Permissions on the File Path

  • Confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control on the data folder, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
  • Right-click the folder → Properties → Security tab → verify/add the service account with Modify/Full Control permissions.
  • Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER (PowerShell, run as Administrator) after permission changes.

Step 4: Manually Remove the File from the Database Metadata

  • If the file is logically empty but SQL Server still can’t drop it, run:
  ALTER DATABASE [YourDB] REMOVE FILE logical_file_name;
  • Check sys.master_files to confirm the file entry no longer exists after the command completes.

Step 5: Exclude SQL Server Data Directories from Antivirus/Backup Scans

  • Add exclusions in Windows Defender (or third-party AV) for the DATA and LOG folders under the SQL Server instance path.
  • Pause any VSS-based backup agents temporarily and retry the shrink/remove operation to rule out file-locking conflicts.

Alternative or Verification Steps

  • Verify success by querying sys.master_files — the removed file should no longer appear, and the physical .ndf/.mdf file should be gone from the filesystem.
  • If the issue persists, take the database offline (ALTER DATABASE [YourDB] SET OFFLINE;), manually delete the file via File Explorer (if unlocked), then bring the database back online (ALTER DATABASE [YourDB] SET ONLINE;) to force cleanup.