Introduction When SQL Server detects database corruption during startup recovery—typically after an unexpected crash, power failure, or disk error—it marks the database as SUSPECT. In this state, the database is inaccessible and no connections can be established until the corruption is resolved.

Symptoms - Database shows `(Suspect)` in SSMS Object Explorer - Error 926: `Database 'mydb' cannot be opened. It has been marked SUSPECT` - SQL Server error log shows `Starting up database 'mydb'` followed by recovery errors - `DBCC CHECKDB` cannot run because the database is inaccessible - Application connections fail with database unavailable errors

Common Causes - Unclean shutdown during active transaction write - Disk subsystem failure corrupting data or log files - I/O subsystem writing incomplete pages (torn pages) - Antivirus or backup software locking database files during write - Hardware failure (failing disk, bad RAID controller cache battery)

Step-by-Step Fix 1. **Check the error log for the root cause": ```sql -- Read the SQL Server error log EXEC xp_readerrorlog 0, 1, N'Suspect', N'mydb';

-- Or check the Windows Event Log -- Look for disk, I/O, or SQL Server errors around the crash time ```

  1. 1.**Attempt emergency repair with EMERGENCY mode":
  2. 2.```sql
  3. 3.-- Set database to EMERGENCY mode (single-user, read-only)
  4. 4.ALTER DATABASE mydb SET EMERGENCY;

-- Check for corruption DBCC CHECKDB('mydb') WITH NO_INFOMSGS, ALL_ERRORMSGS; ```

  1. 1.**If CHECKDB reports errors, attempt repair":
  2. 2.```sql
  3. 3.-- Set to single-user mode
  4. 4.ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Attempt repair (REPAIR_REBUILD - no data loss) DBCC CHECKDB('mydb', REPAIR_REBUILD);

-- If that fails, use REPAIR_ALLOW_DATA_LOSS (LAST RESORT) DBCC CHECKDB('mydb', REPAIR_ALLOW_DATA_LOSS);

-- Return to multi-user mode ALTER DATABASE mydb SET MULTI_USER; ```

  1. 1.**If repair fails, restore from backup":
  2. 2.```sql
  3. 3.-- Restore from the most recent full backup
  4. 4.RESTORE DATABASE mydb
  5. 5.FROM DISK = N'D:\Backups\mydb_full.bak'
  6. 6.WITH NORECOVERY;

-- Apply the most recent log backup RESTORE LOG mydb FROM DISK = N'D:\Backups\mydb_log.trn' WITH RECOVERY; ```

  1. 1.**Check disk health to prevent recurrence":
  2. 2.```bash
  3. 3.# Windows
  4. 4.chkdsk E: /f /r
  5. 5.# Check RAID controller battery and disk SMART status
  6. 6.wmic diskdrive get status
  7. 7.`

Prevention - Implement regular full, differential, and log backup schedules - Test backup restoration quarterly - Use UPS and graceful shutdown procedures - Monitor disk health with SMART and RAID controller alerts - Enable `PAGE_VERIFY CHECKSUM` on all databases - Use battery-backed RAID controller cache - Set up alerting for SQL Server error log entries with severity 17+