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

by

Troubleshooting Guide: Error 5520 – FILESTREAM Container Upgrade Failure

Why This Error Happens

Error 5520 occurs when SQL Server attempts to recalculate the size of a FILESTREAM container during a database upgrade and encounters inconsistencies between the FILESTREAM directory structure and the internal metadata tables. This typically stems from corrupted or missing filestream data files, orphaned files left behind by an improperly terminated previous operation, or insufficient NTFS permissions for the SQL Server service account on the FILESTREAM data container path. It can also surface when the underlying storage volume has unresolved disk errors or when antivirus/backup software has locked files inside the FILESTREAM container during the upgrade process.

Step-by-Step Resolution Guide

Step 1: Review the Full Error Log Context

  • Open SQL Server Configuration Manager or navigate to the log path (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) and search for entries immediately preceding Error 5520.
  • Look for related errors such as 5901, 5903, or OS-level I/O errors (Event ID 823/824) that indicate the specific file or directory causing the recalculation failure.
  • Note the exact container ID and database ID referenced in the error to isolate the affected FILESTREAM filegroup.

Step 2: Verify NTFS Permissions and Service Account Access

  • Locate the FILESTREAM data container folder, usually under a path like D:\SQLData\FileStreamData\<GUID>.
  • Right-click the folder, go to Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
  • Run the following PowerShell command to reset permissions if needed:
  icacls "D:\SQLData\FileStreamData" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F" /T
  • Restart the SQL Server service after applying permission changes.

Step 3: Check for File-Level Corruption or Orphaned Files

  • Run DBCC CHECKDB with the DATA_PURITY option against the affected database to identify corruption:
  DBCC CHECKDB (YourDatabaseName) WITH DATA_PURITY;
  • Manually inspect the FILESTREAM container folder for orphaned .tmp or zero-byte files that do not correspond to any active LOB pointer in the sys.filestream_tombstone system view.
  • If orphaned files are found, back up the database first, then remove confirmed orphaned files only after validating they are not referenced by FILESTREAM_PENDING_DELETE operations.

Step 4: Disable Interfering Services and Retry the Upgrade

  • Temporarily exclude the FILESTREAM data directory from antivirus real-time scanning (common in Windows Defender via Virus & Threat Protection Settings > Exclusions).
  • Ensure no backup agent or VSS snapshot process is actively locking files in the container directory.
  • Re-run the database upgrade or ALTER DATABASE command that triggered the original FILESTREAM container upgrade.

Step 5: Rebuild the FILESTREAM Filegroup (If Corruption Persists)

  • If the container remains unrecoverable, create a new FILESTREAM filegroup and migrate data using INSERT INTO ... SELECT from the old filestream column to the new one.
  • Drop the corrupted filegroup only after confirming all data has been successfully migrated and verified.

Alternative or Verification Steps

  • Confirm resolution by restarting the SQL Server service and checking the ERRORLOG for successful FILESTREAM initialization messages (no further 5520 entries on startup).
  • If the issue persists after all steps, restore the database from the last known good backup taken prior to the upgrade attempt, then reapply the upgrade in a maintenance window with antivirus and VSS services fully disabled.