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

by

Why This Error Happens

Error 5509 occurs because SQL Server’s FILESTREAM filegroup treats its data file as a container directory rather than a traditional binary data file, meaning SIZE and FILEGROWTH properties are structurally incompatible with it. This typically happens when a DBA copies syntax from a standard ROWS filegroup ADD FILE statement and forgets to strip these properties out when defining the FILESTREAM container. Since FILESTREAM data is stored directly on the NTFS volume rather than inside the managed .mdf/.ndf file structure, SQL Server has no mechanism to pre-allocate size or manage growth increments for it.

Step-by-Step Resolution Guide

Step 1: Review the Current T-SQL Syntax

  • Open the script that triggered the error in SQL Server Management Studio (SSMS) connected to your SQL Server 2025 instance.
  • Locate the ADD FILE clause associated with the FILESTREAM_ON filegroup definition.
  • Identify instances of SIZE =, MAXSIZE =, or FILEGROWTH = within that specific file block.

Step 2: Strip Invalid Properties from the File Definition

  • Remove the SIZE, MAXSIZE, and FILEGROWTH arguments entirely from the FILESTREAM file entry.
  • Ensure the syntax only retains NAME and FILENAME (which should point to a folder path, not a file extension like .ndf).
  • Example of corrected syntax:
  ALTER DATABASE MyDB
  ADD FILE (NAME = FileStreamData, FILENAME = 'C:\SQLData\FileStreamContainer')
  TO FILEGROUP FileStreamGroup1;

Step 3: Verify the Target Directory Permissions

  • Confirm the path specified in FILENAME does not already exist as a folder prior to execution; SQL Server must create this directory itself.
  • Check that the SQL Server service account has “Full Control” NTFS permissions on the parent directory (e.g., C:\SQLData\) via Windows Security > Advanced Sharing Permissions.
  • Re-run the corrected ALTER DATABASE or CREATE DATABASE script.

Step 4: Restart the SQL Server Service (If Required)

  • If changes were made to the FILESTREAM configuration level (Instance level) rather than just the script, restart the SQL Server (MSSQLSERVER) service via services.msc.
  • Alternatively, run net stop MSSQLSERVER followed by net start MSSQLSERVER in an elevated Command Prompt.

Alternative or Verification Steps

  • Verify the fix by querying sys.database_files in SSMS; the size and growth columns for the FILESTREAM row should display as 0, confirming the properties were successfully omitted.
  • If the container folder was partially created during a failed attempt, manually delete the specific sub-folder (not the parent drive) via File Explorer before re-attempting the ALTER DATABASE command, as a pre-existing empty folder can sometimes cause secondary conflicts.