Fix SQL Server Error 823 – Step-by-Step

What is SQL Server Error 823?

Error 823 (Operating system error 123456: The system detected a page fault... file: C:\SQLData\mydb.mdf) hits when SQL Server can't read or write a database page. This isn't a soft error—it means the data on disk is physically corrupt or the storage subsystem gave up.

I've seen this on Dell PowerEdge servers with failing RAID controllers, on Azure VMs with temporary storage, and on aging SATA drives. The fix depends on whether the corruption is isolated or widespread. Let's walk through it, starting with the quickest check.

Quick Check (30 seconds) – Is It a Transient Issue?

Before you panic, rule out a flaky connection or temporary Windows problem.

Open SQL Server Management Studio (SSMS) and connect to the instance.
Run this query against the master database:
```
SELECT SERVERPROPERTY('Edition') AS Edition;
```
Expected outcome: You'll see your SQL edition (e.g., Standard, Enterprise). No error means SSMS is talking to SQL.
Check if the drive where the database file lives is accessible. Open File Explorer, navigate to the folder listed in the error (like C:\SQLData\). Try opening a file there. If you can't see the drive, you've got a hardware or OS issue, not just a SQL problem.
Restart the SQL Server service from Services.msc. Right-click the service name (likely "SQL Server (MSSQLSERVER)"), choose Restart. Wait 30 seconds, then retry the query that threw the error. If it works now, the issue was intermittent—maybe a disk driver hiccup. If it still fails, move on.

Real-world trigger: A power surge took down the SAN switch for 2 seconds, causing a write-back cache to incomplete the I/O. Restarting SQL flushed the bad state but didn't fix the underlying corruption. You'll need the next fix.

Moderate Fix (5 minutes) – Find and Repair a Single Corrupt Page

If the error keeps happening, you need to locate the corrupted page and decide whether to repair or restore.

Run DBCC CHECKDB with the suspect database name. For example:
```
USE [YourDatabaseName];
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS;
```
Expected outcome: You'll see messages like "CHECKDB found 0 allocation errors and 0 consistency errors in database..." If it shows corruption (e.g., "Page (1:345) is marked as suspect"), note the page ID.

What the output means: If there's only one or two corrupt pages (and they're not in critical system tables), you can try to repair them. If there are hundreds, skip to the advanced fix.
Set the database to single-user mode so you can repair it:
```
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
```
Expected outcome: The command completes, and other connections get kicked out. If it hangs, run SELECT * FROM sys.dm_exec_requests WHERE DB_NAME(database_id) = 'YourDatabaseName' to see what's blocking. Kill that session with KILL spid.
Run repair with ALLOW_DATA_LOSS (because you might lose the corrupt row):
```
DBCC CHECKDB (N'YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
```
Expected outcome: Messages like "Repair: The page (1:345) has been deallocated. One row of data was lost." That's one lost row—annoying but not fatal.
Set the database back to multi-user mode:
```
ALTER DATABASE [YourDatabaseName] SET MULTI_USER;
```
Expected outcome: Database is online and usable. Test with a simple SELECT 1.

When this fails: If DBCC CHECKDB reports corruption in system tables (like sys.sysschobjs), repair won't work—it'll say "repair is not allowed." You need the advanced fix.

Advanced Fix (15+ minutes) – Full Restore from Backup

If the corruption is deep or widespread, you're restoring from backup. No shortcuts here—you need a clean backup file.

Check your backup chain. Run this to see your last full, differential, and log backups:
```
SELECT database_name, type, backup_start_date, first_lsn, last_lsn
FROM msdb.dbo.backupset
WHERE database_name = N'YourDatabaseName'
ORDER BY backup_start_date DESC;
```
Expected outcome: A list of backups. You need the most recent full backup (type = 'D'), then the latest differential (type = 'I') if you have one, then all log backups (type = 'L') since the differential.
Take a tail-log backup of the current (corrupt) database so you don't lose recent changes:
```
BACKUP LOG [YourDatabaseName] TO DISK = N'C:\SQLBackups\YourDB_tail.trn' WITH NORECOVERY;
```
Expected outcome: The database goes into Restoring state. If this fails with error 823, you can't take a tail log—skip to the next step without it.
Restore the full backup with NORECOVERY:
```
RESTORE DATABASE [YourDatabaseName] FROM DISK = N'C:\SQLBackups\YourDB_full.bak'
WITH REPLACE, NORECOVERY;
```
Expected outcome: Messages saying "The database is now in a restoring state." If you get an error about the backup being from an older version, you'll need to restore to a different server with the same SQL version, then copy.

Restore the latest differential (if you have one):

RESTORE DATABASE [YourDatabaseName] FROM DISK = N'C:\SQLBackups\YourDB_diff.bak'
WITH NORECOVERY;

Restore all log backups in order, ending with the tail log if you took it. For each log file, run:
```
RESTORE LOG [YourDatabaseName] FROM DISK = N'C:\SQLBackups\YourDB_log.trn'
WITH NORECOVERY;
```
Expected outcome: Each restore should show LSN ranges that connect. If one fails with "The log in this backup set begins at LSN... which is too late," you're out of order—check the backup_start_date.
Bring the database online:
```
RESTORE DATABASE [YourDatabaseName] WITH RECOVERY;
```
Expected outcome: Database comes online. Run DBCC CHECKDB again to confirm no corruption. It should return clean.

If you have no backup: You're in a bad spot. The only option is to use a third-party tool like Stellar Phoenix or ApexSQL Recover to extract readable data from the damaged MDF file. Expect partial data, and don't trust the tool blindly—audit the results.

Preventing Future Error 823

Error 823 is almost always a storage problem. Do these things now so you don't have to deal with it later:

Run CHKDSK /R on the drive weekly (scheduled via Task Scheduler).
Use SQL Server's built-in DBCC CHECKDB as a weekly maintenance job. I schedule mine for Sunday 2 AM.
Switch to a RAID 10 or RAID 5 array with a battery-backed cache. Cheap drives on a single spindle will bite you.
Test your backups monthly. A backup file that corrupts can't help you. I restore to a test instance and run DBCC CHECKDB on it.

That's it. You've got the steps. Start with the quick check, then work your way down. If you get stuck, drop me a line—I've fixed this hundreds of times.