SQL SERVER 中如何快速检查数据完整性

1.  DBCC CHECKDB检查数据库完整性

1.1.   工具介绍

在 SQL Server 中，DBCC CHECKDB命令可检查数据库的逻辑和物理完整性，定期运行有助于提早发现并解决潜在的问题。

具体功能包括：

1)       一致性检查：验证数据库中各种对象的一致性，包括表、索引等。

2)       检测和修复：识别数据库中的错误或损坏，并在需要时尝试修复这些问题。

3)       物理检查：检查数据库页和页内结构的物理完整性。

4)       跨对象一致性：确保关系数据库中不同对象之间的一致性，例如，确保索引与数据是一致的。

参考：

https://learn.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-2016

1.2.   常见使用示例

执行权限所需角色：Sysadmin, db_owner

### 检查当前数据库。

DBCC CHECKDB

### 检查指定数据库，不丢失数据，可修复索引。

DBCC CHECKDB ('YourDatabaseName', REPAIR_REBUILD);

### 检查当前数据库取消显示信息性消息。

DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS;

### 大型生产可提高速度

DBCC CHECKDB WITH PHYSICAL_ONLY; GO

### 建议在事务中进行数据修复操作，示例：

BEGIN TRANSACTION

DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS`

COMMIT TRANSACTION

如不想接受操作的结果，可撤销本次事务。

ROLLBACK TRANSACTION

1.3.   DBCC CHECKDB的基本语法

基本语法：

DBCC CHECKDB

    [ ( database_name | database_id | 0

        [ , NOINDEX

        | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]

    ) ]

    [ WITH

        {

            [ ALL_ERRORMSGS ]

            [ , EXTENDED_LOGICAL_CHECKS ]

            [ , NO_INFOMSGS ]

            [ , TABLOCK ]

            [ , ESTIMATEONLY ]

            [ , { PHYSICAL_ONLY | DATA_PURITY } ]

            [ , MAXDOP = number_of_processors ]

        }

    ]

]

选项说明：

1)       database_name | database_id | 0  ：要为其运行完整性检查的数据库的名称或 ID。 如果未指定，或者指定为 0，则使用当前数据库。 数据库名称必须符合标识符规则。

2)       NOINDEX：指定不对用户表的非聚集索引执行会占用很大系统开销的检查。 此选项将减少总执行时间。 NOINDEX 不影响系统表，因为总是对系统表索引执行完整性检查。

3)       REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD ：指定 DBCC CHECKDB 修复发现的错误。 仅将 REPAIR 选项作为最后手段使用。 指定的数据库必须处于单用户模式，才能使用以下修复选项之一。

a)       `REPAIR_ALLOW_DATA_LOSS`：尝试修复所有错误，可能会导致数据丢失。

b)       `REPAIR_REBUILD`：修复没有数据丢失风险的错误，如重建坏掉索引。

c)       REPAIR_FAST：保留该语法只是为了向后兼容。 未执行修复操作。

4)       ALL_ERRORMSGS：显示针对每个对象报告的所有错误。 默认情况下显示所有错误消息。 指定或省略此选项都不起作用。

5)       EXTENDED_LOGICAL_CHECKS：如果兼容性级别为 100（在 SQL Server 2008 (10.0.x) 中引入），则此选项对索引视图、XML 索引和空间索引（如果存在）执行逻辑一致性检查。

6)       NO_INFOMSGS：取消显示所有信息性消息。

1.4.   使用建议

备份：在进行任何修复操作前，务必先备份数据库。

性能影响：`DBCC CHECKDB` 可能会对性能产生影响，建议在低负载时间执行。

安全测试：在生产系统上修复错误之前，可以先在测试环境中进行验证。

由于 DBCC 修复选项不可用于内存优化表，因此必须定期备份数据库并测试备份。 如果内存优化表中出现数据完整性问题，必须从上次已知的正确备份中还原。

1.5.   执行输出示例

输出示例：