Source: Dev.to

当 SQL Server 页面损坏时会发生什么？

在 SQL Server 中，数据是以 页面（8 KB）为基本存储单元。当其中的某个页面出现物理或逻辑损坏时，整个数据库的完整性都会受到威胁。本文将介绍：

• 页面损坏的常见原因
• 损坏页面的典型症状
• 如何检测和定位受影响的页面
• 修复或恢复的可行方案

---

常见的页面损坏原因

---

损坏页面的典型症状

1. 查询错误

   • Msg 824, Level 24, State 2, Line 1

     发生了 I/O 错误，数据库 'MyDB' 中的页面 (1:12345) 无法读取。

   • Msg 823, Level 24, State 2

     读取页面时出现校验和错误。

2. 启动时的恢复失败

   • SQL Server 在启动恢复阶段卡住，日志中出现类似 “Page verification failed” 的信息。

3. DBCC CHECKDB 报错

   • 检查时出现 “Consistency errors”，并指向特定的文件 ID 与页面号。

4. 性能异常

   • 受损页面所在的索引或表出现 锁等待、死锁或查询超时，因为引擎不断尝试读取错误页面。

---

如何检测受影响的页面

1. 使用 DBCC CHECKDB

DBCC CHECKDB ('MyDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• NO_INFOMSGS：只返回错误信息，避免大量正常信息淹没关键错误。
• ALL_ERRORMSGS：确保所有错误都被列出，包括页面校验和错误。

2. 查看错误日志

EXEC xp_readerrorlog 0, 1, N'Page verification failed';

• 通过搜索关键字 “Page verification failed”、“checksum” 或 “I/O error” 可以快速定位受影响的文件与页面号。

3. 使用 DBCC PAGE（仅在受控环境下使用）

DBCC TRACEON (3604);
DBCC PAGE ('MyDatabase.mdf', 1, 12345, 3);

• 3604 将输出重定向到客户端。
• 参数 1 为文件 ID，12345 为页面号，3 为详细模式。

注意：DBCC PAGE 是内部调试工具，生产环境中不建议随意使用。

---

修复或恢复的可行方案

1. 从备份恢复

如果有 最近且完整的备份，最安全的做法是：

RESTORE DATABASE MyDatabase FROM DISK = 'C:\Backups\MyDatabase.bak' WITH REPLACE;

• WITH REPLACE 允许覆盖现有数据库。
• 恢复后立即运行 DBCC CHECKDB 确认没有残留错误。

2. 使用 DBCC CHECKDB 的修复选项

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ('MyDatabase') WITH REPAIR_ALLOW_DATA_LOSS;
ALTER DATABASE MyDatabase SET MULTI_USER;

强烈建议：在执行任何修复前，先 完整备份当前数据库（即使它已经损坏），以防修复过程导致更大范围的破坏。

3. 手动导出健康数据

如果只是一小部分页面受损，可以：

1. 创建新数据库

   CREATE DATABASE MyDatabase_Recovery;

2. 使用 INSERT INTO … SELECT 将健康表/索引复制过去。
3. 对受影响的对象使用 SELECT INTO 或 bcp 导出后再导入。

这样可以保留大部分数据，同时绕过损坏页面。

4. 使用第三方恢复工具

市面上有 SQL Server 专用的恢复工具（如 ApexSQL Recover、Stellar Repair for MS SQL），它们可以在不依赖 DBCC 的情况下尝试恢复损坏的页面。但这些工具往往是付费的，且成功率取决于损坏的严重程度。

---

防止页面损坏的最佳实践

1. 硬件层面的可靠性

   • 使用 RAID 10 或 存储级别的镜像。
   • 定期运行磁盘健康检查（如 chkdsk、SMART 监控）。

2. SQL Server 配置

   • 开启 PAGE_VERIFY CHECKSUM（默认）或 PAGE_VERIFY TORNADO。
   • 为关键数据库启用 DBCC CHECKDB 的定期作业（建议每日一次，业务低峰时段）。

3. 备份策略

   • 完整备份 + 差异备份 + 事务日志备份 的组合。
   • 将备份存放在 不同的物理介质或云端，并定期进行 恢复演练。

4. 监控与告警

   • 使用 SQL Server Agent 或 第三方监控平台（如 SCOM、Redgate）捕获 824/823 错误并即时通知。

5. 内存检测

   • 部署 ECC 内存，并定期运行 Windows Memory Diagnostic 或硬件厂商提供的工具。

---

小结

• 页面损坏会导致查询错误、恢复失败以及潜在的数据丢失。
• 通过 DBCC CHECKDB、错误日志以及 DBCC PAGE 可以快速定位受影响的页面。
• 首选恢复方式 是从最近的完整备份进行恢复；若无备份，则在评估风险后使用 REPAIR_ALLOW_DATA_LOSS 或手动迁移健康数据。
• 预防措施（硬件可靠性、正确的 PAGE_VERIFY 设置、定期备份与检查）是避免页面损坏的根本手段。

保持警惕、做好监控、定期演练恢复计划，才能在页面损坏的突发事件中将业务影响降到最低。

页面结构

数据文件被划分为 8 KB 的页面，每个页面包括：

这些组件合计 8 192 字节（8 KB） —— 标准的 SQL Server 页面大小。

Source: …

扩展

extent 是由八个连续的 8 KB 页面组成的集合（总计 64 KB）。
扩展有两种类型：

• 统一扩展 – 所有八个页面都分配给单个对象。
• 混合扩展 – 这些页面由多个对象共享。

此设计有助于优化空间使用和性能。您可以使用以下查询查看数据库的当前分配模式：

SELECT [name], [is_mixed_page_allocation_on]
FROM   sys.databases;

物理文件

SQL Server 将页面存储在属于以下三类之一的物理文件中：

这些文件协同工作以管理存储并确保数据完整性。

Source: …

检查分配和结构完整性

让我们验证 AdventureWorks2022 数据库的分配和结构完整性（跳过索引检查），并将重点放在 Person.Person 表上进行损坏测试。

-- 检查表的逻辑一致性
DBCC CHECKTABLE ('AdventureWorks2022.Person.Person');

-- 检查整个数据库的分配问题（不检查索引）
DBCC CHECKALLOC ('AdventureWorks2022', NOINDEX);

确定表使用的页

可以使用 DBCC IND 找出表占用的页：

DBCC IND ('AdventureWorks2022', 'Person.Person', -1);

输出会显示表的数据在各页和文件中的分布情况。

检查特定页

获取页号后，使用 DBCC PAGE 查看其内部结构（页头、行偏移、数据行等）。例如，检查文件 1 中的页 1314：

DBCC PAGE ('AdventureWorks2022', 1, 1314, 1) WITH TABLERESULTS;

模拟页面损坏

⚠️ 仅在测试或开发环境中运行以下内容。故意损坏数据可能导致信息丢失。

-- Put the database in single‑user mode
ALTER DATABASE [AdventureWorks2022] SET SINGLE_USER WITH NO_WAIT;

-- Overwrite a byte on the page to simulate corruption
DBCC WRITEPAGE ('AdventureWorks2022', 1, 1314, 0, 1, 0x11, 1);

验证损坏

运行 DBCC CHECKDB 或 DBCC CHECKTABLE 再次查看 SQL Server 如何检测到该问题。（不要 在此使用 CHECKALLOC，因为它仅检查分配情况，而不检查数据完整性。）

DBCC CHECKDB ('AdventureWorks2022');
DBCC CHECKTABLE ('AdventureWorks2022.Person.Person');

您应该会收到类似以下的错误：

Msg 824, Level 24, State 2, Line 32
SQL Server detected a logical consistency‑based I/O error: incorrect checksum
(expected: 0x246e3cb2; actual: 0x24663cb2). It occurred during a ...

清理

演示完成后，将数据库恢复为多用户模式；如果需要，可从干净的备份中恢复，以消除损坏。

ALTER DATABASE [AdventureWorks2022] SET MULTI_USER;

摘要

• Pages 是由页眉、数据行和槽数组组成的 8 KB 单元。
• Extents 将八个页面组合在一起，且可以是统一的也可以是混合的。
• 你可以使用 DBCC IND 检查页面分配情况，并使用 DBCC PAGE 查看页面内容。
• 通过 DBCC WRITEPAGE 模拟损坏，可观察 DBCC CHECKDB/DBCC CHECKTABLE 如何检测完整性问题。

理解页面以及如何操作页面，可让你更深入了解 SQL Server 的存储引擎，并帮助诊断和解决底层数据问题。

Problem Overview

read of page (1:1314) in database ID 5 at offset 0x00000000a44000 in file 
'C:\MSSQL\DATA\AdventureWorks2022.mdf'.  

Additional messages in the SQL Server error log or operating system error log may 
provide more detail. This is a severe error condition that threatens database 
integrity and must be corrected immediately. Complete a full database 
consistency check (DBCC CHECKDB).  

This error can be caused by many factors; for more information, see SQL Server 
Books Online.

关于 DBCC 命令的更多信息，请参阅我之前的主题：
MSSQL DBCC – How good are they really? 👌

修复选项

🤞 第一个选项 – REPAIR_REBUILD

DBCC CHECKTABLE ('Person.Person', REPAIR_REBUILD);

REPAIR_REBUILD 可以修复某些类型的索引损坏或结构不一致，而不会导致数据丢失。它比 REPAIR_ALLOW_DATA_LOSS 更安全，因为它会重建损坏的索引和结构，而不是删除数据。

本例的结果

Msg 8928, Level 16, State 1, Line 39
Object ID 2101582525, index ID 1, partition ID 72057594049724416,
alloc unit ID 72057594057523200 (type In‑row data): Page (1:1314) could not be processed.
The repair level on the DBCC statement caused this repair to be bypassed.

Msg 8939, Level 16, State 98, Line 39
Table error: Object ID 2101582525, index ID 1, partition ID 72057594049724416,
alloc unit ID 72057594057523200 (type In‑row data), page (1:1314). 
Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -4.
Repairing this error requires other errors to be corrected first.

Msg 8976, Level 16, State 1, Line 39
Table error: Object ID 2101582525, index ID 1, partition ID 72057594049724416,
alloc unit ID 72057594057523200 (type In‑row data). Page (1:1314) was not seen in the scan 
although its parent (1:1568) and previous (1:1313) refer to it. Check any previous errors.
Repairing this error requires other errors to be corrected first.

Msg 8978, Level 16, State 1, Line 39
Table error: Object ID 2101582525, index ID 1, partition ID 72057594049724416,
alloc unit ID 72057594057523200 (type In‑row data). Page (1:1315) is missing a reference 
from previous page (1:1314). Possible chain linkage problem.
Repairing this error requires other errors to be corrected first.

There are 19967 rows in 3807 pages for object "Person.Person".
CHECKTABLE found 0 allocation errors and 4 consistency errors in table 
'Person.Person' (object ID 2101582525).

结论： REPAIR_REBUILD 无法修复此问题。

😥 第二个选项 – REPAIR_ALLOW_DATA_LOSS

DBCC CHECKTABLE ('Person.Person', REPAIR_ALLOW_DATA_LOSS);

警告： 这应该是你的 最后手段，仅在你接受可能的数据丢失时使用。
前提条件： 在运行该命令之前先进行 完整备份（BACKUP DATABASE）。

🤔 第三个选项 – 页面恢复

页面恢复通过从备份中提取干净的页面来替换损坏的页面，避免完整数据库恢复。当你拥有最近的完整备份和日志备份时，这种方法效果最佳。

1. 确认可疑页面

SELECT * FROM msdb.dbo.suspect_pages;

2. 恢复损坏的页面

RESTORE DATABASE [AdventureWorks2022] PAGE = '1:1314' 
FROM DISK = N'Z:\BCKP\w19$SQ\AdventureWorks2022\FULL\w19$SQ_AdventureWorks2022_FULL_20260214_122842.bak' 
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5;

3. 应用后续日志备份以保持事务日志链完整

RESTORE LOG [AdventureWorks2022] 
FROM DISK = N'Z:\BCKP\w19$SQ\AdventureWorks2022\LOG\w19$SQ_AdventureWorks2022_LOG_20260214_122900.trn' 
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5, ONLINE;   -- ONLINE 仅限 Enterprise 版

4. 进行一次新的日志备份（可选但推荐）

BACKUP LOG [AdventureWorks2022] 
TO DISK = N'Z:\BCKP\w19$SQ\AdventureWorks2022\LOG\LOG_AdventureWorks2022' 
WITH INIT;

5. 将数据库置于联机状态

RESTORE LOG [AdventureWorks2022] 
FROM DISK = N'Z:\BCKP\w19$SQ\AdventureWorks2022\LOG\LOG_AdventureWorks2022' 
WITH RECOVERY;

6. 验证修复结果

DBCC CHECKDB ('AdventureWorks2022');

提示： 对于大型数据库，页面级恢复结合日志恢复的方式，比完整数据库恢复要少得多的中断。

修复选项概览

选择最符合您环境、备份策略和数据丢失容忍度的方法。在尝试任何修复之前务必先进行备份。

🫡 第四选项：远程恢复并重新创建

将数据库恢复到新位置并重新创建该表（适用于小型数据库）。

如果损坏仅限于单个表或数据库的一小部分，并且数据库大小允许，保持简单处理。另一种方法是将数据库恢复到新位置，然后在主数据库上重新创建损坏的表。在此过程中，您还可以创建触发器，以在 SQL Server 仍然允许执行 INSERT、UPDATE 或 DELETE 时拒绝任何交互。

CREATE TRIGGER tr_tableDENY ON Person.Person
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    RAISERROR ('This table is temporarily locked for investigation.', 16, 1);
    ROLLBACK TRANSACTION;
END

Final Advice

• 定期运行 DBCC CHECKDB。这对于维护数据库完整性至关重要，但不要仅仅依赖 DBCC 检查。
• 设置实时警报以检测损坏。

Suggested Monitoring Approach

创建一个 SQL Agent 作业，持续监视 msdb.dbo.suspect_pages 表的新条目，并在检测到任何损坏时立即发送警报。警报处理完毕后，你可以清理该表。

由于 Error 823、824 和 825 与数据库页的损坏相关，请同样为这些特定错误设置警报。这样一来，当它们发生时，你会立即收到通知。

1. 在 SQL Server Agent 中，右键单击 Alerts → New Alert。
2. 为每个错误号创建一个警报。
3. 指定一个操作员（电子邮件）作为响应方式。

Error Descriptions

• Error 823 – 读取或写入磁盘时的 I/O 错误。

• Error 824 – 在读取页面数据时检测到逻辑一致性错误。

• Error 825 – I/O 操作期间的页面失败。

• 页面和扩展架构指南

• 数据库文件和文件组

• MSSQL DBCC – 它们真的有多好？

• SQL Server 错误 823 – 数据库引擎错误