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

by

Troubleshooting Guide: SQL Server Error 12100 — FILESTREAM Filegroup Conflict with Snapshot Isolation

Why This Error Happens

This error occurs because FILESTREAM technology is fundamentally incompatible with row-versioning based isolation levels at the database level. SQL Server 2025 blocks the creation of FILESTREAM filegroups when either READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION is set to ON, since FILESTREAM data is stored as BLOBs on the NTFS file system rather than in the traditional buffer pool, making it unable to participate in the versioning store. To resolve this, the snapshot isolation options must be disabled before the FILESTREAM filegroup can be added, and re-enabled afterward only if your application logic permits it.

Step-by-Step Resolution Guide

Step 1: Identify the Current Database Isolation Settings

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.
  • Run the following query to check the current state of your target database:
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc 
FROM sys.databases 
WHERE name = 'YourDatabaseName';
  • Confirm that either is_read_committed_snapshot_on is 1 or snapshot_isolation_state_desc is ON.

Step 2: Disable Snapshot Isolation Options

  • Ensure no active transactions are running against the database; you may need to set the database to SINGLE_USER mode temporarily to avoid blocking.
  • Execute the following T-SQL commands to turn off both settings:
ALTER DATABASE YourDatabaseName SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE YourDatabaseName SET ALLOW_SNAPSHOT_ISOLATION OFF;
  • If the command hangs, check for open transactions using sys.dm_tran_active_transactions and terminate blocking sessions via KILL [session_id].

Step 3: Add the FILESTREAM Filegroup

  • With snapshot isolation disabled, retry the ALTER DATABASE command to add the FILESTREAM filegroup:
ALTER DATABASE YourDatabaseName 
ADD FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM;
  • Verify the Windows Server 2025 FILESTREAM feature is enabled via SQL Server Configuration Manager under SQL Server Services > [Your Instance] > Properties > FILESTREAM tab; ensure “Enable FILESTREAM for Transact-SQL access” is checked.

Step 4: Re-enable Snapshot Isolation (If Required)

  • If your application logic strictly requires snapshot isolation for concurrency control, you may re-enable it now that the filegroup exists:
ALTER DATABASE YourDatabaseName SET READ_COMMITTED_SNAPSHOT ON;
  • Note: Attempting to re-enable this will likely fail again if FILESTREAM data already exists, as the restriction works both ways; plan your concurrency strategy accordingly.

Alternative or Verification Steps

  • Verification: Query sys.filegroups and sys.database_files to confirm the new FILESTREAM filegroup and associated container path were created successfully.
  • Alternative: If snapshot isolation is a hard requirement for your application, consider isolating FILESTREAM data into a separate dedicated database that does not use READ_COMMITTED_SNAPSHOT, linking it back to your primary transactional database via application-layer logic or linked server queries.