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

by

Troubleshooting Guide: SQL Server Error 5504

Why This Error Happens

Error 5504 occurs when a CREATE TABLE or ALTER TABLE statement attempts to use a CONTAINS clause that references the PRIMARY filegroup for FILESTREAM data, rather than a dedicated FILESTREAM filegroup. SQL Server 2025 strictly enforces that FILESTREAM data columns must be bound to a filegroup explicitly created with the CONTAINS FILESTREAM option, since the PRIMARY filegroup is reserved for standard row and page data. This typically happens due to a misconfigured table definition script, a database migration that didn’t properly map filegroups, or a developer copying DDL syntax without updating the filegroup reference.

Step-by-Step Resolution Guide

Step 1: Verify Existing Filegroup Configuration

  • Open SQL Server Management Studio (SSMS) and connect to your Windows Server 2025 instance.
  • Run the following query to check current filegroups:
SELECT name, type_desc, is_default 
FROM sys.filegroups;
  • Confirm whether a FILESTREAM-type filegroup (type_desc = 'FD') already exists in the target database.

Step 2: Create a Dedicated FILESTREAM Filegroup

  • If no FILESTREAM filegroup exists, create one using ALTER DATABASE:
ALTER DATABASE [YourDatabase] 
ADD FILEGROUP FileStreamGroup CONTAINS FILESTREAM;
  • Associate a physical path with the new filegroup:
ALTER DATABASE [YourDatabase] 
ADD FILE (NAME = FileStreamData, FILENAME = 'C:\SQLData\FileStreamData') 
TO FILEGROUP FileStreamGroup;
  • Ensure the path (e.g., C:\SQLData\FileStreamData) exists and the SQL Server service account has write permissions.

Step 3: Correct the Table Definition

  • Modify your CREATE TABLE or ALTER TABLE script to reference the new FILESTREAM filegroup instead of PRIMARY.
  • Example corrected syntax:
CREATE TABLE dbo.Documents (
    DocID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
    DocContent VARBINARY(MAX) FILESTREAM NULL
) FILESTREAM_ON FileStreamGroup;
  • Re-run the script and confirm Error 5504 no longer appears.

Step 4: Enable FILESTREAM at the Instance Level (If Not Already Enabled)

  • Open SQL Server Configuration Manager on Windows Server 2025.
  • Navigate to SQL Server Services, right-click your instance, and select Properties.
  • Go to the FILESTREAM tab and enable “FILESTREAM for Transact-SQL access.”
  • Restart the SQL Server service for changes to take effect.

Alternative or Verification Steps

  • Run sp_configure 'filestream access level' to confirm the value is set to 1 or 2, verifying FILESTREAM is active at the instance level.
  • If the error persists, drop and recreate the table using the corrected FILESTREAM_ON clause pointing explicitly to the FILESTREAM filegroup rather than relying on database defaults.