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

by

Troubleshooting Guide: DBCC SHRINKFILE Error 2563 on FILESTREAM Containers

Why This Error Happens

Error 2563 occurs because SQL Server enforces a strict rule that FILESTREAM filegroup containers cannot be shrunk using standard DBCC SHRINKFILE options such as TARGETSIZE or NOTRUNCATE. The FILESTREAM container is treated differently from standard data or log files because it maps directly to a directory structure on the NTFS/ReFS file system rather than a single allocatable file. SQL Server 2025 requires the EMPTYFILE option exclusively, as this safely migrates FILESTREAM BLOB data to other files in the filegroup before the container can be removed, preventing data corruption.

Step-by-Step Resolution Guide

Step 1: Identify the Correct File ID and Logical Name

  • Run the following query to locate the specific FILESTREAM logical file name and ID within the affected database:
  USE [YourDatabaseName];
  GO
  SELECT file_id, name, physical_name, type_desc 
  FROM sys.database_files 
  WHERE type_desc = 'FILESTREAM';
  • Note the name or file_id value, as this is required for the correct syntax in Step 2.

Step 2: Execute DBCC SHRINKFILE with the EMPTYFILE Option

  • Replace the standard shrink command with the required EMPTYFILE syntax. Do not combine this with TARGETSIZE or PERCENT arguments.
  • Use the following command structure in SQL Server Management Studio (SSMS) or Azure Data Studio:
  DBCC SHRINKFILE (N'FileStreamLogicalName', EMPTYFILE);
  GO
  • Verify that the command completes without throwing Error 2563 regarding invalid option combinations.

Step 3: Remove the Emptied Container from the Filegroup

  • Once the EMPTYFILE command succeeds, the container is logically empty but still exists in the filegroup metadata.
  • Execute an ALTER DATABASE statement to physically remove the file entry:
  ALTER DATABASE [YourDatabaseName] 
  REMOVE FILE [FileStreamLogicalName];
  GO
  • Check the Windows Server 2025 file explorer path (typically under C:\SQLData\FileStreamContainers\) to confirm the specific GUID-named subfolder has been removed or marked for deletion.

Step 4: Restart the SQL Server Service to Clear Locks

  • If the ALTER DATABASE command hangs or fails due to file locks, restart the SQL Server (MSSQLSERVER) service via the Services console (services.msc) on Windows Server 2025.
  • This clears any lingering file handles on the FILESTREAM directory that might be blocking the metadata cleanup.

Alternative or Verification Steps

  • Verify the Fix: Query sys.database_files again after the restart to confirm the specific file_id associated with the problematic container no longer appears in the result set.
  • Alternative Approach: If EMPTYFILE continues to fail due to active transactions, check sys.dm_tran_locks for blocking sessions on the FILESTREAM filegroup, kill the blocking Session ID (SPID) using KILL <SPID>, and re-attempt the EMPTYFILE command.