【SQL Server学习笔记】DBCC命令3：状态验证

最新推荐文章于 2021-08-03 11:58:33 发布

LongRui888

最新推荐文章于 2021-08-03 11:58:33 发布

阅读量1.9k

点赞数

分类专栏：【SQL Server管理】文章标签：数据库 sql server database constraints table

本文链接：https://blog.csdn.net/sqlserverdiscovery/article/details/7824716

版权

【SQL Server管理】专栏收录该内容

58 篇文章 6 订阅

订阅专栏

状态验证：针对数据库、文件组、表、索引、数据库分页的分配、数据库元数据等进行验证。

如checkdb、checkfilgroup、checktable、checkalloc、checkconstraints、checkident、checkcatalog等。

1、DBCC CHECKALLOC检查磁盘空间分配结构的一致性。

DBCC CHECKALLOC 可使用内部数据库快照来提供执行这些检查所需的事务的一致性。如果无法创建快照，或指定了 TABLOCK，则 DBCC CHECKALLOC 将尝试获取排他 (X) 数据库锁，以获取所需的一致性。

/*=============================================

DBCC CHECKALLOC 
[
        ( database_name | database_id | 0 
      [ , NOINDEX 
      | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
        )
    [ WITH 
        { 
          [ ALL_ERRORMSGS ]
          [ , NO_INFOMSGS ] 
          [ , TABLOCK ] 
          [ , ESTIMATEONLY ] 
        }
    ]
]

===============================================*/

--不检查非聚集索引
DBCC CHECKALLOC('WCC',NOINDEX)     

--要修复数据必须把数据库置于单用户模式
ALTER DATABASE WCC
SET SINGLE_USER

--会尝试修复表或索引视图，在处理过程中会带来丢失数据风险
DBCC CHECKALLOC('WCC',
                REPAIR_ALLOW_DATA_LOSS)  

--REPAIR_FAST   :保留语法只是为了向后兼容。未执行修复操作。
--REPAIR_REBUILD:执行修复不会有丢失数据的风险
DBCC CHECKALLOC('WCC',
                REPAIR_REBUILD)


DBCC CHECKALLOC('WCC')
WITH ALL_ERRORMSGS,  --显示所有错误
     NO_INFOMSGS,    --取消所有信息性消息和关于所用空间的报告
     TABLOCK,        --不使用内部数据快照,而是使用排他表锁
     ESTIMATEONLY    --显示执行所有其他选项时需要的估计的tempdb空间

2、DBCC CHECKDB 通过执行下列操作检查指定数据库中所有对象的逻辑和物理完整性：

对数据库运行 DBCC CHECKALLOC。
对数据库中的每个表和视图运行 DBCC CHECKTABLE。
对数据库运行 DBCC CHECKCATALOG。
验证数据库中每个索引视图的内容。
使用 FILESTREAM 在文件系统中存储 varbinary(max) 数据时，验证表元数据和文件系统目录和文件之间的链接级一致性。
验证数据库中的 Service Broker 数据。

这意味着不必从 DBCC CHECKDB 单独运行 DBCC CHECKALLOC、DBCC CHECKTABLE 或 DBCC CHECKCATALOG 命令。

特别需要注意的是：

A、仅将 REPAIR 选项作为最后手段使用。若要修复错误，建议您通过备份进行还原。

B、如果必须使用 REPAIR，则运行不带有修复选项的 DBCC CHECKDB 来查找要使用的修复级别。如果使用REPAIR_ALLOW_DATA_LOSS 级别，则建议您在运行带有此选项的 DBCC CHECKDB 之前备份数据库。

C、修复操作不会考虑表本身或表之间可能存在的任何约束。如果指定的表与一个或多个约束有关，建议您在修复操作后运行 DBCC CHECKCONSTRAINTS。DBCC CHECKDB 不检查禁用的索引。

D、在 SQL Server Management Studio 中，返回的最大错误消息数为 1000。使用 Management Studio 时，如果指定了 ALL_ERRORMSGS，则可能需要多次执行 DBCC CHECKDB 才能得到完整的错误列表。当您指定 ALL_ERRORMSGS 时，我们建议您使用 sqlcmd 实用工具来执行 DBCC 命令，或计划 SQL Server 代理作业来执行该命令并将输出定向到文件。这两种方法中的任一种都可以确保执行该命令一次即可报告所有错误消息。

C:\Documents and Settings\Administrator>sqlcmd -E -S PC0627JVC\MSSQLSERVER2008
1> DBCC CHECKDB('MASTER');
2> GO

/*===========================================================

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 } ]
        }
    ]
]

=============================================================*/

--1.不检查非聚集索引
DBCC CHECKDB('WC',NOINDEX )
  
  
--2.要修复数据必须把数据库置于单用户状态
ALTER DATABASE WC
SET SINGLE_USER
with rollback immediate

--尝试修复报告的所有错误,这些修复可能会导致一些数据丢失
DBCC CHECKDB('WC',
             REPAIR_ALLOW_DATA_LOSS)  
                

--3.REPAIR_REBUILD:执行不会丢失数据的修复。
--这包括快速修复（如修复非聚集索引中缺少的行）以及更耗时的修复（如重新生成索引）。
DBCC CHECKDB('WC',
             REPAIR_fast)


--4.EXTENDED_LOGICAL_CHECKS则将对索引视图、XML 索引和空间索引（如果存在）执行逻辑检查。
--默认情况下，先执行物理一致性检查，然后执行逻辑一致性检查。
--如果还指定了 NOINDEX，则仅执行逻辑检查。             
DBCC CHECKDB('WC')
WITH EXTENDED_LOGICAL_CHECKS

--5.
DBCC CHECKDB('WC')
WITH ALL_ERRORMSGS,  --显示所有错误
     NO_INFOMSGS,    --取消所有信息性消息和关于所用空间的报告
     TABLOCK,        --不使用内部数据快照,而是使用排他表锁
     ESTIMATEONLY    --显示执行所有其他选项时需要的估计的tempdb空间


/*====================================================================
6.PHYSICAL_ONLY:
将检查限制为页和记录标头的物理结构完整性以及数据库的分配一致性。
设计该检查是为了以较小的开销检查数据库的物理一致性，
但它还可以检测会危及用户数据安全的残缺页、校验和错误以及常见的硬件故障。

DBCC CHECKDB完成运行所需的时间可能比早期版本要长得多。
出现此现象的原因是：
1.逻辑检查更加全面。
2.要检查的某些基础结构更为复杂。
3.引入了许多新的检查以包含新增功能。

因此，使用PHYSICAL_ONLY选项可能会大幅减少对较大数据库运行DBCC CHECKDB所需的时间，
所以对需要频繁检查的生产系统，建议使用此选项。
我们仍然建议完整地定期执行 DBCC CHECKDB。
这些运行的执行频率取决于各业务和生产环境特定的因素。

PHYSICAL_ONLY 始终表示 NO_INFOMSGS，不能与任何一个修复选项一同使用。
指定 PHYSICAL_ONLY会使DBCC CHECKDB跳过对FILESTREAM数据的所有检查。
=============================================================================*/
DBCC CHECKDB('WC')
WITH PHYSICAL_ONLY


/*====================================================================
7.DATA_PURITY:
使 DBCC CHECKDB 检查数据库中是否存在无效或越界的列值。
例如，DBCC CHECKDB检测日期和时间值大于或小于datetime数据类型的可接受范围的列，
或者小数位数或精度值无效的 decimal 或近似numeric数据类型列。

对于在SQL Server 2005及更高版本中创建的数据库，默认情况下将启用列值完整性检查，
并且不需要使用DATA_PURITY选项。
但对于从SQL Server的早期版本升级的数据库，默认情况下不启用列值检查，
直到DBCC CHECKDB WITH DATA_PURITY已在数据库中正确运行为止。
然后，DBCC CHECKDB 将默认检查列值完整性。
有关从SQL Server 的早期版本升级数据库会对 CHECKDB 有何影响的详细信息，
请参阅本主题的“备注”部分。

DATA_PURITY可以与其他选项同时使用。
如果指定了PHYSICAL_ONLY，则不执行列完整性检查。

无法使用DBCC修复选项来纠正该选项所报告的验证错误。
有关手动更正这些错误的信息，请参阅知识库文章923247：解决SQL Server2005中的DBCC错误2570。

=============================================================================*/
DBCC CHECKDB('WC')
WITH DATA_PURITY

3、DBCC CHECKTABLE检查组成表或索引视图的所有页和结构的完整性。其中的选项和DBCC CHECKDB都相同。

对于指定的表，DBCC CHECKTABLE 将检查以下内容：

是否已正确链接索引、行内、LOB 以及行溢出数据页。
索引是否按照正确的顺序排列。
各指针是否一致。
每页上的数据是否合理（包括计算列）。
页面偏移量是否合理。
基表的每一行是否在每个非聚集索引中具有匹配的行，以及非聚集索引的每一行是否在基表中具有匹配的行。
已分区表或索引的每一行是否都位于正确的分区中。
使用 FILESTREAM 将 varbinary(max) 数据存储在文件系统中时，文件系统与表之间是否保持链接级一致性。

对索引执行逻辑一致性检查

对索引进行的逻辑一致性检查因数据库兼容级别而异，如下所示：

如果兼容级别为 100 (SQL Server 2008) 或更高：
- 除非指定 NOINDEX，否则 DBCC CHECKTABLE 将对单个表及其所有非聚集索引同时执行物理和逻辑一致性检查。但是，在默认情况下，仅对 XML 索引、空间索引和索引视图执行物理一致性检查。
- 如果指定了 WITH EXTENDED_LOGICAL_CHECKS，则将对索引视图、XML 索引和空间索引（如果存在）执行逻辑检查。默认情况下，先执行物理一致性检查，然后执行逻辑一致性检查。如果还指定了 NOINDEX，则仅执行逻辑检查。
  
  这些逻辑一致性检查可对索引对象的内部索引表及其引用的用户表进行交叉检查。为了查找外部行，将构造内部查询来对内部表和用户表的完整交集执行查询。运行此查询可能会对性能产生很大影响，并且无法跟踪其进度。因此，建议您仅在以下情况下才指定 WITH EXTENDED_LOGICAL_CHECKS：怀疑存在与物理损坏无关的索引问题，或者已关闭页级校验并且怀疑存在列级硬件损坏。
- 如果索引为筛选索引，DBCC CHECKDB 将执行一致性检查以验证索引项是否满足筛选谓词的要求。
如果兼容级别为 90 或更低，则除非指定 NOINDEX，否则 DBCC CHECKTABLE 将对单个表或索引视图及其所有非聚集索引和 XML 索引同时执行物理和逻辑一致性检查。不支持空间索引。

内部数据库快照

DBCC CHECKTABLE 使用内部数据库快照提供其执行这些检查必需的事务一致性。有关详细信息，请参阅了解数据库快照中的稀疏文件大小和 DBCC (Transact-SQL) 中的“DBCC 内部数据库快照用法”部分。

如果无法创建快照，或指定了 TABLOCK，则 DBCC CHECKTABLE 将获取一个共享表锁来获得所需的一致性。

如果对 tempdb 运行 DBCC CHECKTABLE，则必须获取一个共享表锁。这是因为，为了提高性能，不允许对tempdb 使用数据库快照。这意味着无法获得所需的事务一致性。

检查和修复 FILESTREAM 数据

对数据库和表启用 FILESTREAM 后，便可选择将 varbinary(max) 二进制大型对象 (BLOB) 存储在文件系统中。对在文件系统中存储 BLOB 的表使用 DBCC CHECKTABLE 时，DBCC 会检查文件系统与数据库之间的链接级一致性。

例如，如果一个表包含使用 FILESTREAM 属性的 varbinary(max) 列，DBCC CHECKTABLE 将检查文件系统目录和文件与表行、表列和列值之间是否存在一对一映射关系。如果指定了 REPAIR_ALLOW_DATA_LOSS 选项，DBCC CHECKTABLE 便可修复损坏。为了修复 FILESTREAM 损坏，DBCC 将删除缺少文件系统数据的任何表行，并将删除未映射到表行、表列或列值的任何目录和文件。

并行检查对象

默认情况下，DBCC CHECKTABLE 对对象执行并行检查。并行度由查询处理器自动确定。最大并行度的配置方式与并行查询相同。若要限制 DBCC 检查可使用的处理器的最大数目，请使用sp_configure。有关详细信息，请参阅 max degree of parallelism 选项。

通过使用跟踪标志 2528 可以禁用并行检查。有关详细信息，请参阅跟踪标志 (Transact-SQL)。

在运行 DBCC CHECKTABLE 期间时，以字节顺序排列的用户定义类型列中存储的字节必须与用户定义类型值的计算序列相等。否则，DBCC CHECKTABLE 例程将报告一致性错误。

了解 DBCC 错误消息

DBCC CHECKTABLE 命令完成后，将向 SQL Server 错误日志中写入一条消息。如果 DBCC 命令成功执行，则该消息指示成功完成以及命令运行的时间。如果 DBCC 命令在完成检查之前由于错误而停止，则该消息将指示命令已终止，并指示状态值和命令运行的时间。下表列出并说明了该消息中可包含的状态值。

状态	说明
0	出现错误号 8930。这指示导致 DBCC 命令终止的元数据损坏。
1	出现错误号 8967。存在一个内部 DBCC 错误。
2	在紧急模式数据库修复过程中出错。
3	这指示导致 DBCC 命令终止的元数据损坏。
4	检测到断言或访问违规。
5	出现终止了 DBCC 命令的未知错误。

错误报告

只要 DBCC CHECKTABLE 检测到损坏错误，就会在 SQL Server LOG 目录中创建一个微型转储文件 (SQLDUMPnnnn.txt)。如果为 SQL Server 实例启用了“功能使用情况数据收集”和“错误报告”功能，该文件将被自动转发给 Microsoft。收集的数据将用于改进 SQL Server 功能。

转储文件包含 DBCC CHECKTABLE 命令的结果以及其他诊断输出数据。该文件拥有任意访问控制列表 (DACL)。只有 SQL Server 服务帐户和sysadmin 角色的成员有权进行访问。默认情况下，sysadmin 角色包含 Windows BUILTIN\Administrators 组和本地管理员组的所有成员。如果数据收集进程失败，DBCC 命令不会失败。

纠正错误

如果 DBCC CHECKTABLE 报告了任何错误，那么，我们建议从数据库备份中还原数据库，而不是使用某个 REPAIR 选项来运行 REPAIR。如果没有备份，则运行 REPAIR 也可以更正报告的错误。要使用的修复选项在报告的错误的末尾处指定。但是，使用 REPAIR_ALLOW_DATA_LOSS 选项更正错误可能需要删除一些页面（从而也删除了数据）。

修复操作可以在用户事务下执行，以允许用户回滚所做的更改。如果回滚修复，数据库仍会包含错误，因而必须通过备份进行还原。全部修复完成后，请备份数据库。

4、DBCC CHECKCATALOG会检查系统表内及表之间的一致性，不过没有修复选项可用，所以只能从最近的数据库备份中恢复。

在执行DBCC CHECKCATALOG时，在操作期间会创建一个内部数据库快照来维护事务一致性。如果因为某些原因不能创建数据库快照，在执行期间将产生排他数据库锁，这会潜在的降低数据查询并发性。

DBCC CHECKCATALOG作为DBCC CHECKDB的一部分执行。

需要注意，对 tempdb 运行 DBCC CHECKCATALOG 不会执行任何检查。这是因为，为了提高性能，不允许对tempdb 使用数据库快照。这意味着无法获得所需的事务一致性。回收服务器以解析任何tempdb 元数据问题。

DBCC CHECKCATALOG 不会检查 FILESTREAM 数据。FILESTREAM 在文件系统中存储二进制大型对象 (BLOB)。

DBCC CHECKCATALOG 
[ 
        ( 
        database_name | database_id | 0
        )
]
    [ WITH NO_INFOMSGS ]

5、DBCC CHECKFILEGROUP检查文件组中所有表的分配和结构完整性。

注意：只是对指定的文件组上的对象进行完整性和分配检查。

对于超大型数据库，执行DBCC CHECKDB可能在时间上是不允许的，作为替代，如果在数据库中使用用户定义的文件组，那么可以使用DBCC CHECKFILEGROUP命令进行周期性的检查，在不同的天中对不同文件组进行检查。

当执行命令时，在操作期间会创建内部数据库快照来维护事务一致性。如果由于某种原因而不能创建数据库快照(或者指定了TABLOCK选项)，命令会在检查表时生成共享表锁，在分配检查时产生排他数据库锁。

由于DBCC CHECKFILEGROUP没有修复选项，所以只能用最近完好的数据库备份来恢复。

DBCC CHECKFILEGROUP 
[
    [ ( { filegroup_name | filegroup_id | 0 } 
        [ , NOINDEX ] 
    ) ]
    [ WITH 
        { 
            [ ALL_ERRORMSGS | NO_INFOMSGS ] 
            [ , TABLOCK ] 
            [ , ESTIMATEONLY ]
            [ , PHYSICAL_ONLY ]  
        } 
    ]
]

6、DBCC CHECKCONSTRAINTS会报告在指定表和约束中发现的所有CHECK或外键约束的违反情况。

这个命令会返回违反约束的数据，从而可以修正违反的约束，但如果使用了NOCHECK禁用了约束，那么这个命令也不会捕捉约束违反情况。

和其他的数据库完整性DBCC命令不同，DBCC CHECKCONSTRAINTS不会在DBCC CHECKDB中运行，所以如果需要标识数据库中数据约束的违反情况，必须单独运行此命令。

DBCC CHECKCONSTRAINTS
[ 
        ( 
        table_name | table_id | constraint_name | constraint_id 
        )
]
    [ WITH 
        [ { ALL_CONSTRAINTS | ALL_ERRORMSGS } ]
    [ , ] [ NO_INFOMSGS ] 
    ]

alter table wct
add constraint ck_wct
check (wcDate <= '2060-01-01')

alter table wct
nocheck constraint ck_wct

insert into wct
values('2060-02-02')

alter table wct 
check constraint all

DBCC CHECKCONSTRAINTS('dbo.wct')

7、DBCC CHECKIDENT：检查指定表的当前标识值，如有必要，则更改标识值。还可以使用 DBCC CHECKIDENT 为标识列手动设置新的当前标识值。

DBCC CHECKIDENT 
( 
        table_name
        [ , { NORESEED | { RESEED [ , new_reseed_value ] } } ]
)
[ WITH NO_INFOMSGS ]

DBCC CHECKIDENT 命令	标识更正或所做的更正
DBCC CHECKIDENT ( table_name, NORESEED )	不重置当前标识值。DBCC CHECKIDENT 将返回标识列的当前标识值和当前最大值。如果这两个值不相同，则应重置标识值，以避免值序列中的潜在错误或空白。
DBCC CHECKIDENT ( table_name ) 或者 DBCC CHECKIDENT ( table_name, RESEED )	如果表的当前标识值小于标识列中存储的最大标识值，则使用标识列中的最大值对其进行重置。
DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )	将当前标识值设置为 new_reseed_value。如果自从创建表以来未在表中插入任何行，或者已使用 TRUNCATE TABLE 语句删除所有行，则在运行 DBCC CHECKIDENT 之后插入的第一行将使用new_reseed_value 作为标识。否则，插入的下一行将使用new_reseed_value + 当前增量值。如果该表不为空，那么将标识值设置为小于标识列中的最大值的数字时，将会出现下列情况之一：如果标识列中存在 PRIMARY KEY 或 UNIQUE 约束，则随后在表中执行插入操作时将生成错误消息 2627，原因是生成的标识值将与现有值冲突。如果不存在 PRIMARY KEY 或 UNIQUE 约束，则随后的插入操作将产生重复的标识值。

DBCC CHECKIDENT 命令

标识更正或所做的更正

DBCC CHECKIDENT ( table_name, NORESEED )

不重置当前标识值。DBCC CHECKIDENT 将返回标识列的当前标识值和当前最大值。如果这两个值不相同，则应重置标识值，以避免值序列中的潜在错误或空白。

DBCC CHECKIDENT ( table_name )

或者

DBCC CHECKIDENT ( table_name, RESEED )

如果表的当前标识值小于标识列中存储的最大标识值，则使用标识列中的最大值对其进行重置。

DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )

将当前标识值设置为 new_reseed_value。如果自从创建表以来未在表中插入任何行，或者已使用 TRUNCATE TABLE 语句删除所有行，则在运行 DBCC CHECKIDENT 之后插入的第一行将使用new_reseed_value 作为标识。否则，插入的下一行将使用new_reseed_value + 当前增量值。

如果该表不为空，那么将标识值设置为小于标识列中的最大值的数字时，将会出现下列情况之一：

如果标识列中存在 PRIMARY KEY 或 UNIQUE 约束，则随后在表中执行插入操作时将生成错误消息 2627，原因是生成的标识值将与现有值冲突。
如果不存在 PRIMARY KEY 或 UNIQUE 约束，则随后的插入操作将产生重复的标识值。

LongRui888

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
【SQL Server学习笔记】DBCC命令3：状态验证

状态验证：针对数据库、文件组、表、索引、数据库分页的分配、数据库元数据等进行验证。如checkdb、checkfilgroup、checktable、checkalloc、checkconstraints、checkident、checkcatalog等。1、DBCC CHECKALLOC检查磁盘空间分配结构的一致性。DBCC CHECKALLOC 可使用内部数据库快照来提供执行这
复制链接

扫一扫