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

by

Troubleshooting Guide: Error 5017 – AUTOGROW Property Cannot Be Modified on FILESTREAM or MEMORYOPTIMIZEDDATA Filegroup

Why This Error Happens

Error 5017 occurs because SQL Server does not support changing the AUTOGROW_ALL_FILES or AUTOGROW_SINGLE_FILE property on filegroups marked as FILESTREAM or MEMORY_OPTIMIZED_DATA. These specialized filegroups manage file growth internally using container-based storage semantics rather than traditional data file autogrow logic. The ALTER DATABASE statement attempting this modification is syntactically valid for regular filegroups but is explicitly blocked by the SQL Server engine for these two filegroup types, regardless of permissions or server configuration.

Step-by-Step Resolution Guide

Step 1: Identify the Filegroup Type Causing the Conflict

  • Run the following query to confirm the filegroup’s type before attempting any ALTER operation:
  • SELECT name, type_desc, is_autogrow_all_files FROM sys.filegroups;
  • Look for FD (FILESTREAM Data) or FX (Memory-Optimized Data) in the type_desc column.
  • Confirm that the filegroup referenced in your failed ALTER DATABASE ... MODIFY FILEGROUP command matches one of these types.

Step 2: Remove the Unsupported ALTER Statement from Scripts

  • Edit any deployment or migration scripts (e.g., DACPAC, SSDT projects, or manual T-SQL scripts) that include a line similar to:
  • ALTER DATABASE [YourDB] MODIFY FILEGROUP [FileStreamGroup] AUTOGROW_ALL_FILES;
  • Delete or comment out this line specifically for filegroups identified in Step 1.
  • Save the script and re-run deployment to confirm the error no longer triggers during execution.

Step 3: Apply AUTOGROW Settings Only to Eligible Filegroups

  • If your goal was to standardize autogrow behavior across the database, apply AUTOGROW_ALL_FILES only to ROWS (standard data) filegroups.
  • Example corrected command:
  • ALTER DATABASE [YourDB] MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES;
  • Leave FILESTREAM and MEMORYOPTIMIZEDDATA filegroups unmanaged by this property, as SQL Server automatically handles their growth via the underlying file system.

Step 4: Validate File Growth Behavior Manually (If Required)

  • For FILESTREAM filegroups, growth is governed by NTFS volume space on Windows Server 2025 rather than SQL Server’s autogrow engine.
  • Check available disk space using File Explorer or Get-PSDrive in PowerShell to ensure the underlying volume has sufficient capacity.
  • For MEMORYOPTIMIZEDDATA filegroups, monitor checkpoint file pair (CFP) growth via sys.dm_db_xtp_checkpoint_files instead of relying on autogrow settings.

Alternative or Verification Steps

  • Re-run SELECT name, type_desc, is_autogrow_all_files FROM sys.filegroups; after deployment to confirm only ROWS type filegroups show is_autogrow_all_files = 1.
  • If the script must remain generic across multiple databases, wrap the ALTER DATABASE statement in a conditional check that filters out FD and FX filegroup types using dynamic SQL before execution.