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

by

Troubleshooting Guide: Error 991 – Failed to Take Host Database Offline Due to Suspect Partition Database

Why This Error Happens

This error occurs when SQL Server 2025 attempts to take a horizontally partitioned (sharded) host database offline, but one or more of its underlying partition databases has been flagged as SUSPECT due to a failed recovery process, corrupted transaction log, or inaccessible data file. Since the host database relies on all partition databases being in a healthy, online state to coordinate a clean offline transition, SQL Server blocks the operation to prevent data inconsistency or metadata corruption across the distributed database topology. Common root causes include unexpected storage disconnects during recovery, disk corruption on the volume hosting the partition’s MDF/LDF files, or insufficient permissions for the SQL Server service account to access the partition database files after a failover.

Step-by-Step Resolution Guide

Step 1: Identify the Suspect Partition Database

  • Open SQL Server Management Studio (SSMS) and connect to the host instance.
  • Run the following query to identify which partition is marked suspect:
  SELECT name, state_desc, is_in_standby 
  FROM sys.databases 
  WHERE state_desc = 'SUSPECT';
  • Cross-reference the partition name with your sharding/partition mapping table to confirm its role in the host database group.

Step 2: Review SQL Server Error Logs for Root Cause

  • Navigate to the default log path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
  • Search for entries timestamped around the failure, looking for keywords like “recovery failed,” “823,” “824,” or “I/O error.”
  • Confirm whether the issue is due to physical disk corruption, a torn page, or an access-denied error related to the SQL Server service account.

Step 3: Attempt to Bring the Partition Database Back Online

  • Try running ALTER DATABASE [PartitionDBName] SET ONLINE; to force a recovery attempt.
  • If the command fails, check EMERGENCY mode access using:
  ALTER DATABASE [PartitionDBName] SET EMERGENCY;
  DBCC CHECKDB ([PartitionDBName]) WITH NO_INFOMSGS;
  • Based on DBCC CHECKDB results, run ALTER DATABASE [PartitionDBName] SET ONLINE or proceed to repair options like REPAIR_REBUILD (avoid REPAIR_ALLOW_DATA_LOSS unless necessary).

Step 4: Restore Partition Database from Backup (If Repair Fails)

  • If DBCC CHECKDB indicates unrecoverable corruption, restore the partition database from the last known good full backup plus transaction log backups.
  • Use: RESTORE DATABASE [PartitionDBName] FROM DISK = 'D:\Backups\PartitionDBName.bak' WITH NORECOVERY, followed by log restores and WITH RECOVERY on the final log file.
  • Ensure the restored partition database is reattached correctly to the host database’s partition mapping metadata.

Step 5: Retry Taking the Host Database Offline

  • Once all partition databases report ONLINE status, retry the original operation:
  ALTER DATABASE [HostDBName] SET OFFLINE;
  • Monitor the SQL Server Error Log in real time to confirm the operation completes without re-triggering Error 991.

Alternative or Verification Steps

  • Run SELECT name, state_desc FROM sys.databases; across all partition databases to confirm every database tied to the host shows ONLINE before retrying the offline command.
  • If the suspect partition cannot be repaired or restored in time, consider temporarily removing it from the partition mapping (if your sharding architecture supports dynamic partition exclusion) so the host database can be taken offline without that specific shard, then reintegrate it once resolved.