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

by

Troubleshooting Guide: SQL Server Error 5099

Why This Error Happens

This error occurs because SQL Server does not support enabling snapshot-based isolation levels (READCOMMITTEDSNAPSHOT or ALLOWSNAPSHOTISOLATION) on a database that contains FILESTREAM filegroups. The underlying conflict is architectural: FILESTREAM data is stored directly on the NTFS file system rather than in the database’s versioned data pages, meaning the storage engine cannot generate the row-versioning data required for snapshot isolation. As a result, the ALTER DATABASE command is blocked at the engine level before any changes are committed.

Step-by-Step Resolution Guide

Step 1: Identify the FILESTREAM Filegroups

  • Open SQL Server Management Studio (SSMS) and connect to your Windows Server 2025 instance.
  • Run the following query to confirm which filegroups are causing the conflict:
SELECT name, type_desc FROM sys.filegroups WHERE type = 'FD';
  • Note the names of these filegroups, as you will need them for migration or removal.

Step 2: Migrate or Remove FILESTREAM Data

  • If the FILESTREAM data is non-essential or can be moved, use SSMS Object Explorer to navigate to Database > Properties > Filegroups and remove the FILESTREAM filegroup reference.
  • If the data must be preserved, migrate it using the FILESTREAM_ON clause during a database copy, or script the data out to standard VARBINARY(MAX) columns using a T-SQL migration script.
  • Ensure no active files are being streamed during this transition to avoid I/O errors.

Step 3: Re-attempt the ALTER DATABASE Command

  • Once the FILESTREAM filegroups are removed or the database is confirmed clean, re-run the isolation command:
ALTER DATABASE [YourDatabaseName] SET READ_COMMITTED_SNAPSHOT ON;
  • If Error 5099 persists, restart the SQL Server service via the Services app (services.msc) on Windows Server 2025 to clear any cached metadata locks.

Step 4: Verify Instance-Level FILESTREAM Configuration

  • Open SQL Server Configuration Manager and select your instance properties.
  • Navigate to the FILESTREAM tab and ensure “Enable FILESTREAM for Transact-SQL access” is unchecked if you intend to permanently disable this feature for the specific database group.
  • Apply the changes and restart the SQL Server (MSSQLSERVER) service for the setting to take effect.

Alternative or Verification Steps

  • Verification: Run SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the value returns 1 (enabled) without triggering an error.
  • Alternative: If FILESTREAM is a mandatory requirement for your application, consider using a separate, dedicated database purely for FILESTREAM storage while keeping your transactional OLTP database (which utilizes snapshot isolation) completely separate from file I/O operations.