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

by

Troubleshooting Guide: SQL Server Error 3402

Why This Error Happens

Error 3402 occurs when SQL Server attempts to run recovery on a database that is in a state incompatible with the recovery process, such as SUSPECT, OFFLINE, EMERGENCY, or RESTORING. This typically happens because a previous recovery attempt failed due to corrupted transaction log files, abrupt shutdowns, or storage-level I/O errors that left the database in an inconsistent state. It can also occur if a restore sequence was interrupted or if the database files are marked read-only at the OS level while SQL Server expects write access.

Step-by-Step Resolution Guide

Step 1: Verify the Current Database State

  • Open SQL Server Management Studio (SSMS) or a query window and run:
    SELECT name, state_desc FROM sys.databases WHERE name = 'YourDBName';
  • Confirm whether the state is SUSPECT, RESTORING, OFFLINE, or EMERGENCY.
  • Check the SQL Server Error Log (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for related messages preceding Error 3402.

Step 2: Place the Database in EMERGENCY Mode

  • Run the following to allow limited access for diagnostics:
    ALTER DATABASE YourDBName SET EMERGENCY;
  • This bypasses normal recovery and allows read-only access to assess damage.
  • Confirm the database is now accessible via sp_helpdb YourDBName.

Step 3: Run Consistency Checks and Repair

  • Set the database to single-user mode to avoid conflicts:
    ALTER DATABASE YourDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Run a full integrity check:
    DBCC CHECKDB ('YourDBName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
  • If corruption is confirmed, apply the repair option:
    DBCC CHECKDB ('YourDBName', REPAIR_ALLOW_DATA_LOSS);
  • Return the database to multi-user mode once repairs complete:
    ALTER DATABASE YourDBName SET MULTI_USER;

Step 4: Validate File Permissions and Disk Health

  • Navigate to the data/log file directory (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
  • Ensure the SQL Server service account has Full Control permissions on the .mdf and .ldf files.
  • Run chkdsk C: /f /r on Windows Server 2025 to rule out underlying disk corruption, then restart the SQL Server service.

Step 5: Restore from Backup if Repair Fails

  • If DBCC CHECKDB cannot resolve the issue, restore the database from the last known good backup:
    RESTORE DATABASE YourDBName FROM DISK = 'D:\Backups\YourDBName.bak' WITH RECOVERY;
  • Apply any available transaction log backups in sequence to minimize data loss.

Alternative or Verification Steps

  • After recovery, run SELECT state_desc FROM sys.databases WHERE name = 'YourDBName'; and confirm the state reads ONLINE.
  • If the issue persists after repair attempts, detach the database (sp_detach_db) and re-attach it using CREATE DATABASE ... FOR ATTACH to force SQL Server to re-evaluate file headers and metadata.