SQL Server Error 823 - Causes & Fixes

Error 823 occurs when SQL Server's I/O completion mechanisms detect that Windows file system APIs have failed during read or write operations. The SQL Server Buffer Manager issues I/O requests through the Windows I/O subsystem, and when these operations fail at the OS level, the I/O completion routines in the SQL Server engine detect the failure and raise error 823.

The error manifests during several critical operations: when the Lazy Writer attempts to flush dirty pages from the buffer pool, when checkpoint processes write dirty pages to disk, when the Log Writer flushes transaction log records, or when user queries require page reads from storage. The Buffer Manager's I/O completion threads monitor each I/O operation's return status and immediately flag any OS-level failures.

In SQL Server 2016 and later, the error reporting includes enhanced diagnostics showing the exact Windows error code, file handle information, and I/O operation type. SQL Server 2019 introduced additional telemetry that correlates 823 errors with specific database files and logical operations. SQL Server 2022 and 2025 provide improved error context including the exact byte offset and operation size that failed.

The underlying causes typically involve storage hardware failures, SAN fabric issues, disk driver problems, or file system corruption. Unlike logical errors that SQL Server can sometimes recover from, 823 errors represent physical I/O subsystem failures that threaten data integrity.

-- Check recent 823 errors in the error log
EXEC xp_readerrorlog 0, 1, '823';

Reviews the current SQL Server error log for all 823 error occurrences, showing timestamps and affected database files.

-- Examine I/O stall statistics by database file
SELECT 
    db_name(database_id) AS database_name,
    file_id,
    io_stall_read_ms,
    io_stall_write_ms,
    io_stall,
    num_of_reads,
    num_of_writes,
    size_on_disk_bytes / 1024 / 1024 AS size_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
WHERE io_stall > 10000
ORDER BY io_stall DESC;

Identifies database files experiencing high I/O latency, which often precedes 823 errors when storage systems are failing.

-- Check for pending I/O requests that might indicate storage issues
SELECT 
    database_id,
    file_id,
    io_stall_read_ms,
    io_pending_ms_ticks,
    scheduler_address
FROM sys.dm_io_pending_io_requests
ORDER BY io_pending_ms_ticks DESC;

Shows I/O operations currently waiting for completion, indicating potential storage subsystem problems.

-- Review buffer pool pages with read/write errors
SELECT 
    database_id,
    file_id,
    page_id,
    page_type_desc,
    page_level,
    is_modified,
    is_in_bpool_extension
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4
  AND is_modified = 1
ORDER BY database_id, file_id, page_id;

Examines modified pages in the buffer pool that might be at risk if I/O errors continue.

-- Check Windows System Event Log for hardware errors
EXEC xp_readerrorlog 0, 2, 'error', 'disk';

Searches the Windows System event log for disk-related errors that correlate with SQL Server 823 errors.

Immediate Database Consistency Check Performs emergency consistency checks on affected databases to assess corruption extent.

-- Run emergency DBCC checks on affected database
-- Replace 'YourDatabase' with the actual database name from the 823 error
USE [YourDatabase];
GO

-- Quick allocation check first
DBCC CHECKALLOC WITH NO_INFOMSGS;

-- If allocation check passes, run full consistency check
-- WARNING: This can be resource-intensive on large databases
DBCC CHECKDB WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS;

Test this script on a restored backup first. The consistency check can consume significant CPU and I/O resources during production hours.

Emergency Read-Only Mode for Data Protection Sets affected databases to read-only to prevent further corruption while addressing storage issues.

-- Set database to read-only to prevent further writes during storage issue resolution
-- Replace 'YourDatabase' with the affected database name
ALTER DATABASE [YourDatabase] SET READ_ONLY WITH ROLLBACK IMMEDIATE;

-- Verify the database is now read-only
SELECT name, is_read_only, state_desc 
FROM sys.databases 
WHERE name = 'YourDatabase';

This immediately stops all write operations. Coordinate with application teams before executing as this will break write functionality.

Force Checkpoint and Buffer Pool Flush Attempts to flush any remaining dirty pages before taking databases offline for storage maintenance.

-- Force checkpoint to flush dirty pages before maintenance
-- Replace 'YourDatabase' with the affected database name
USE [YourDatabase];
GO
CHECKPOINT;

-- Clear buffer pool for the specific database to force re-reads after storage fix
DECLARE @db_id INT = DB_ID('YourDatabase');
DBCC DROPCLEANBUFFERS;

-- Verify buffer pool status
SELECT 
    COUNT(*) as pages_in_buffer,
    SUM(CAST(free_space_in_bytes AS BIGINT))/1024/1024 AS free_space_mb
FROM sys.dm_os_buffer_descriptors
WHERE database_id = @db_id;

Execute during maintenance windows only. Flushing buffers impacts performance as pages must be re-read from storage.

Post-Repair Validation Script Validates database integrity after storage issues are resolved and databases are brought back online.

-- Comprehensive post-repair validation
-- Replace 'YourDatabase' with the repaired database name
USE [YourDatabase];
GO

-- Set database back to read-write if it was set to read-only
ALTER DATABASE [YourDatabase] SET READ_WRITE;

-- Run full consistency check with repair options if needed
-- CAUTION: REPAIR_ALLOW_DATA_LOSS can cause data loss
DBCC CHECKDB WITH NO_INFOMSGS;

-- Verify database operational status
SELECT 
    name,
    state_desc,
    is_read_only,
    recovery_model_desc
FROM sys.databases 
WHERE name = 'YourDatabase';

Only use REPAIR_ALLOW_DATA_LOSS as a last resort after confirming backups are available and business approves potential data loss.

Configure proactive I/O monitoring using sys.dm_io_virtual_file_stats to establish baseline I/O latency thresholds. Set up automated alerts when read latency exceeds 20ms or write latency exceeds 10ms consistently. Implement Windows Performance Toolkit (WPT) monitoring for storage queue depth and disk response times.

Establish storage redundancy through proper RAID configurations, SAN multipathing, and regular storage hardware health monitoring. Configure SQL Server to use multiple tempdb files on separate storage spindles to reduce I/O contention. Enable instant file initialization and set appropriate autogrowth settings to minimize metadata operations during high I/O periods.

Deploy comprehensive backup validation procedures including regular RESTORE VERIFYONLY operations and periodic restore testing to alternate systems. Implement backup compression and backup checksums to detect I/O issues before they impact production databases. Configure database page verification to CHECKSUM to enable early detection of I/O corruption.

Set up integration between SQL Server error log monitoring and storage management systems to correlate 823 errors with hardware alerts. Configure Windows Error Reporting and System Center Operations Manager to automatically escalate storage-related errors to infrastructure teams. Establish maintenance windows for proactive storage subsystem health checks including SAN fabric analysis and disk firmware updates.