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

by

Troubleshooting Guide: SQL Server Error 5540 — FILESTREAM Column ROWGUIDCOL Constraint Issue

Why This Error Happens

This error occurs because FILESTREAM data operations require a ROWGUID column that uniquely identifies each row at the storage level, and SQL Server enforces strict rules on this column to maintain file-to-row mapping integrity. Specifically, the ROWGUIDCOL property is assigned to a column that is either nullable or lacks a UNIQUE constraint (or PRIMARY KEY), which means SQL Server cannot guarantee a one-to-one relationship between table rows and their corresponding FILESTREAM data on disk. Without this guarantee, methods like PathName() or OpenSqlFilestream cannot reliably resolve which file corresponds to which row, so SQL Server blocks the operation entirely.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Table and Column

  • Run the following query to locate the FILESTREAM-enabled table and its associated ROWGUID column:
  SELECT c.name AS ColumnName, c.is_nullable, t.name AS TableName
  FROM sys.columns c
  JOIN sys.tables t ON c.object_id = t.object_id
  WHERE c.is_rowguidcol = 1;
  • Confirm whether is_nullable returns 1 (true), which indicates the root cause.
  • Also check if a UNIQUE or PRIMARY KEY constraint exists on this column via SQL Server Management Studio (SSMS) under Table > Keys.

Step 2: Alter the Column to Enforce NOT NULL

  • Open a new query window in SSMS connected to the affected database.
  • Modify the column definition to disallow NULL values:
  ALTER TABLE dbo.YourTableName
  ALTER COLUMN RowGuidColumn UNIQUEIDENTIFIER NOT NULL;
  • Replace YourTableName and RowGuidColumn with your actual table and column names.
  • If existing NULL values are present, update them first using NEWID() before applying the constraint.

Step 3: Add a Unique Constraint or Primary Key

  • Apply a UNIQUE constraint (or PRIMARY KEY, if not already defined) on the ROWGUIDCOL column:
  ALTER TABLE dbo.YourTableName
  ADD CONSTRAINT UQ_RowGuidColumn UNIQUE (RowGuidColumn);
  • Ensure no duplicate GUID values exist beforehand by running a GROUP BY check:
  SELECT RowGuidColumn, COUNT(*) 
  FROM dbo.YourTableName 
  GROUP BY RowGuidColumn 
  HAVING COUNT(*) > 1;

Step 4: Restart the SQL Server Service

  • Open Services.msc on Windows Server 2025 / Windows 11.
  • Locate SQL Server (MSSQLSERVER) or your named instance, right-click, and select Restart.
  • This ensures the FILESTREAM filegroup metadata cache is refreshed and recognizes the new constraints.

Alternative or Verification Steps

  • Re-run the original application method (e.g., OpenSqlFilestream or PathName()) against the table to confirm Error 5540 no longer appears in SSMS or the Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.
  • If the error persists, drop and recreate the table with the ROWGUIDCOL, NOT NULL, and UNIQUE constraint defined together in the original CREATE TABLE statement, then migrate data using INSERT INTO ... SELECT to avoid legacy constraint conflicts.