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

by

Troubleshooting Guide: Error 3987 — SNAPSHOT ISOLATION is Always Enabled in This Database

Why This Error Happens

This error occurs because the database already has ALLOW_SNAPSHOT_ISOLATION set to ON, which is a persistent, database-level setting that does not reset between sessions or connections. When an attempt is made to re-run the ALTER DATABASE command to enable snapshot isolation on a database where it is already active, SQL Server raises Error 3987 instead of silently ignoring the redundant command. This is typically caused by a misunderstanding of the current database state, an automated deployment or migration script running without a pre-check, or a conflict between multiple scripts/tools attempting to configure isolation levels simultaneously.

Step-by-Step Resolution Guide

Step 1: Verify the Current Snapshot Isolation State

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the target instance.
  • Run the following query to check the current state of the database:
  SELECT name, snapshot_isolation_state, snapshot_isolation_state_desc 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';
  • A result of 1 or ON confirms that snapshot isolation is already active and no further action is needed to “enable” it.

Step 2: Review and Correct the Deployment Script

  • Locate the source of the command, typically found in a .sql deployment file, a DACPAC post-deployment script, or a DevOps pipeline (e.g., Azure DevOps YAML or Jenkinsfile).
  • Modify the script to include a conditional check before executing the ALTER DATABASE command to prevent the error from halting the pipeline:
  IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'YourDatabaseName' AND snapshot_isolation_state = 0)
  BEGIN
      ALTER DATABASE YourDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;
  END
  • Save the script and ensure it is updated in the source control repository (e.g., Git) to prevent future occurrences.

Step 3: Check for Conflicting Automation Tools

  • Audit SQL Server Agent Jobs via SSMS under SQL Server Agent > Jobs to ensure no other scheduled job is attempting to toggle this setting.
  • Check for third-party monitoring tools or ORM frameworks (like Entity Framework migrations) that might be forcing this setting on every application startup.
  • Disable or update the redundant job/script to remove the duplicate ALTER DATABASE call.

Step 4: Restart the Target Service (If State Appears Stuck)

  • If the sys.databases view shows inconsistent data compared to the actual behavior, restart the SQL Server service to clear the cache.
  • Open Command Prompt as Administrator on Windows Server 2025 and run:
  net stop MSSQLSERVER
  net start MSSQLSERVER
  • Re-verify the state using the query from Step 1 immediately after the service restarts.

Alternative or Verification Steps

  • Verification: Run a test transaction using SET TRANSACTION ISOLATION LEVEL SNAPSHOT; followed by a SELECT statement to ensure the database accepts the isolation level without throwing errors.
  • Alternative: If the setting needs to be toggled (e.g., for maintenance), first disable it using ALTER DATABASE YourDatabaseName SET ALLOW_SNAPSHOT_ISOLATION OFF;, ensure no active snapshot transactions are running, and then re-enable it successfully without triggering Error 3987.