迫不得已花落去，数据丢失时时来；何当共谈完整性，却话巴山夜雨时----详解SQL Server 数据库库完整性检查和置疑修复

时间  2019-12-05 标签 迫不得已 花 落 数据 丢失 时时 共 谈 完整性 话 巴山夜雨 时 详解 sql server 数据库 库 完整性 检查 置疑 修复


前情提要

数据库完整性！node

这不是SQL Server独有，有点用户认为是SQL Server设计的问题出现数据库不完整，准确的告诉你，全部的数据库系统，都会存在这问题。 就是数据库出现不完整。 web

数据库完整性是指数据库中数据在逻辑上的一致性、正确性、有效性和相容性。SQL  Server 中完整性检查包含以下：sql

  • 检查指定数据库的磁盘空间分配结构的一致性。数据库

  • 检查组成表或索引视图的全部页和结构的完整性。编程

  • 检查指定数据库内的目录一致性。   多线程

  • 验证数据库中每一个索引视图的内容。 运维

  • 使用 FILESTREAM 在文件系统中存储 varbinary(max) 数据时，验证表元数据和文件系统目录和文件之间的连接级一致性。 ide

  • 验证数据库中的 Service Broker 数据。 优化

故事插曲

上周才发生，一个客户的数据库在进行了某些操做后，数据库“置疑”， 提示须要进行数据库完整性检查，全部业务都停滞了。ui

而客户数据库是TB级别，进行完整性检查超过8个小时。业务至少中止了10几个小时。这个客户的事件还算是特殊状况发生

另一个客户，则是在平常日志错误已经有一些错误报告，没有任何人员进行维护工做。 某一天终于数据库质疑。也就是中止服务了，而后就是丢失数据修复....

这两个故事讲的是几乎不少客户曾经碰到的问题，平常维护工做不作完整性检查，某一天就会发生。 是时候来说讲完整性检查

完整性检查最佳实践

命令


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 ]    
       }    
   ]    
]   
这看起来复杂， 
例：
DBCC CHECKDB;  --检查当前数据库
DBCC CHECKDB (AdventureWorks2012, NOINDEX);--检查 AdventureWorks2012 不检查 nonclustered indexes
DBCC CHECKDB WITH NO_INFOMSGS; --如下示例检查当前数据库，并取消全部信息性消息
DBCC CHECKDB (AdventureWorks2012,REPAIR_ALLOW_DATA_LOSS ) --容许数据丢失的修复

检查结果集以下相似：

DWConfiguration的 DBCC 结果。
Service Broker 消息 9675，状态 1: 已分析的消息类型: 14。
Service Broker 消息 9676，状态 1: 已分析的服务约定: 6。
Service Broker 消息 9667，状态 1: 已分析的服务: 3。
Service Broker 消息 9668，状态 1: 已分析的服务队列: 3。
Service Broker 消息 9669，状态 1: 已分析的会话端点: 0。
Service Broker 消息 9674，状态 1: 已分析的会话组: 0。
Service Broker 消息 9670，状态 1: 已分析的远程服务绑定: 0。
Service Broker 消息 9605，状态 1: 已分析的会话优先级: 0。
sys.sysrscols的 DBCC 结果。
对象“sys.sysrscols”在 15 页中找到 1264 行。
sys.sysrowsets的 DBCC 结果。
对象“sys.sysrowsets”在 3 页中找到 169 行。
sys.sysclones的 DBCC 结果。
对象“sys.sysclones”在 0 页中找到 0 行。
sys.sysallocunits的 DBCC 结果。
对象“sys.sysallocunits”在 3 页中找到 200 行。
sys.sysfiles1的 DBCC 结果。
对象“sys.sysfiles1”在 1 页中找到 2 行。
sys.sysseobjvalues的 DBCC 结果。
对象“sys.sysseobjvalues”在 1 页中找到 1 行。
sys.syspriorities的 DBCC 结果。
对象“sys.syspriorities”在 0 页中找到 0 行。
sys.sysdbfrag的 DBCC 结果。
对象“sys.sysdbfrag”在 0 页中找到 0 行。
sys.sysfgfrag的 DBCC 结果。
对象“sys.sysfgfrag”在 0 页中找到 0 行。
sys.sysdbfiles的 DBCC 结果。
对象“sys.sysdbfiles”在 1 页中找到 2 行。
sys.syspru的 DBCC 结果。
对象“sys.syspru”在 0 页中找到 0 行。
sys.sysbrickfiles的 DBCC 结果。
对象“sys.sysbrickfiles”在 0 页中找到 0 行。
sys.sysphfg的 DBCC 结果。
对象“sys.sysphfg”在 1 页中找到 1 行。
sys.sysprufiles的 DBCC 结果。
对象“sys.sysprufiles”在 1 页中找到 2 行。
sys.sysftinds的 DBCC 结果。
对象“sys.sysftinds”在 0 页中找到 0 行。
sys.sysowners的 DBCC 结果。
对象“sys.sysowners”在 1 页中找到 15 行。
sys.sysdbreg的 DBCC 结果。
对象“sys.sysdbreg”在 0 页中找到 0 行。
sys.sysprivs的 DBCC 结果。
对象“sys.sysprivs”在 1 页中找到 179 行。
sys.sysschobjs的 DBCC 结果。
对象“sys.sysschobjs”在 34 页中找到 2446 行。
sys.syscsrowgroups的 DBCC 结果。
对象“sys.syscsrowgroups”在 0 页中找到 0 行。
sys.sysextsources的 DBCC 结果。
对象“sys.sysextsources”在 0 页中找到 0 行。
sys.sysexttables的 DBCC 结果。
对象“sys.sysexttables”在 0 页中找到 0 行。
sys.sysextfileformats的 DBCC 结果。
对象“sys.sysextfileformats”在 0 页中找到 0 行。
sys.sysmultiobjvalues的 DBCC 结果。
对象“sys.sysmultiobjvalues”在 0 页中找到 0 行。
sys.syscolpars的 DBCC 结果。
对象“sys.syscolpars”在 18 页中找到 1047 行。
sys.sysxlgns的 DBCC 结果。
对象“sys.sysxlgns”在 0 页中找到 0 行。
sys.sysxsrvs的 DBCC 结果。
对象“sys.sysxsrvs”在 0 页中找到 0 行。
sys.sysnsobjs的 DBCC 结果。
对象“sys.sysnsobjs”在 1 页中找到 1 行。
sys.sysusermsgs的 DBCC 结果。
对象“sys.sysusermsgs”在 0 页中找到 0 行。
sys.syscerts的 DBCC 结果。
对象“sys.syscerts”在 1 页中找到 1 行。
sys.sysrmtlgns的 DBCC 结果。
对象“sys.sysrmtlgns”在 0 页中找到 0 行。
sys.syslnklgns的 DBCC 结果。
对象“sys.syslnklgns”在 0 页中找到 0 行。
sys.sysxprops的 DBCC 结果。
对象“sys.sysxprops”在 1 页中找到 1 行。
sys.sysscalartypes的 DBCC 结果。
对象“sys.sysscalartypes”在 1 页中找到 34 行。
sys.systypedsubobjs的 DBCC 结果。
对象“sys.systypedsubobjs”在 0 页中找到 0 行。
sys.sysidxstats的 DBCC 结果。
对象“sys.sysidxstats”在 5 页中找到 227 行。
sys.sysiscols的 DBCC 结果。
对象“sys.sysiscols”在 2 页中找到 433 行。
sys.sysendpts的 DBCC 结果。
对象“sys.sysendpts”在 0 页中找到 0 行。
sys.syswebmethods的 DBCC 结果。
对象“sys.syswebmethods”在 0 页中找到 0 行。
sys.sysbinobjs的 DBCC 结果。
对象“sys.sysbinobjs”在 1 页中找到 23 行。
sys.sysaudacts的 DBCC 结果。
对象“sys.sysaudacts”在 0 页中找到 0 行。
sys.sysobjvalues的 DBCC 结果。
对象“sys.sysobjvalues”在 23 页中找到 236 行。
sys.syscscolsegments的 DBCC 结果。
对象“sys.syscscolsegments”在 0 页中找到 0 行。
sys.syscsdictionaries的 DBCC 结果。
对象“sys.syscsdictionaries”在 0 页中找到 0 行。
sys.sysclsobjs的 DBCC 结果。
对象“sys.sysclsobjs”在 1 页中找到 16 行。
sys.sysrowsetrefs的 DBCC 结果。
对象“sys.sysrowsetrefs”在 0 页中找到 0 行。
sys.sysremsvcbinds的 DBCC 结果。
对象“sys.sysremsvcbinds”在 0 页中找到 0 行。
sys.sysxmitqueue的 DBCC 结果。
对象“sys.sysxmitqueue”在 0 页中找到 0 行。
sys.sysrts的 DBCC 结果。
对象“sys.sysrts”在 1 页中找到 1 行。
sys.sysconvgroup的 DBCC 结果。
对象“sys.sysconvgroup”在 0 页中找到 0 行。
sys.sysdesend的 DBCC 结果。
对象“sys.sysdesend”在 0 页中找到 0 行。
sys.sysdercv的 DBCC 结果。
对象“sys.sysdercv”在 0 页中找到 0 行。
sys.syssingleobjrefs的 DBCC 结果。
对象“sys.syssingleobjrefs”在 1 页中找到 187 行。
sys.sysmultiobjrefs的 DBCC 结果。
对象“sys.sysmultiobjrefs”在 1 页中找到 124 行。
sys.sysguidrefs的 DBCC 结果。
对象“sys.sysguidrefs”在 0 页中找到 0 行。
sys.sysfoqueues的 DBCC 结果。
对象“sys.sysfoqueues”在 0 页中找到 0 行。
sys.syschildinsts的 DBCC 结果。
对象“sys.syschildinsts”在 0 页中找到 0 行。
sys.syscompfragments的 DBCC 结果。
对象“sys.syscompfragments”在 0 页中找到 0 行。
sys.sysftsemanticsdb的 DBCC 结果。
对象“sys.sysftsemanticsdb”在 0 页中找到 0 行。
sys.sysftstops的 DBCC 结果。
对象“sys.sysftstops”在 0 页中找到 0 行。
sys.sysftproperties的 DBCC 结果。
对象“sys.sysftproperties”在 0 页中找到 0 行。
sys.sysxmitbody的 DBCC 结果。
对象“sys.sysxmitbody”在 0 页中找到 0 行。
sys.sysfos的 DBCC 结果。
对象“sys.sysfos”在 0 页中找到 0 行。
sys.sysqnames的 DBCC 结果。
对象“sys.sysqnames”在 1 页中找到 98 行。
sys.sysxmlcomponent的 DBCC 结果。
对象“sys.sysxmlcomponent”在 1 页中找到 100 行。
sys.sysxmlfacet的 DBCC 结果。
对象“sys.sysxmlfacet”在 1 页中找到 112 行。
sys.sysxmlplacement的 DBCC 结果。
对象“sys.sysxmlplacement”在 1 页中找到 19 行。
sys.sysobjkeycrypts的 DBCC 结果。
对象“sys.sysobjkeycrypts”在 1 页中找到 1 行。
sys.sysasymkeys的 DBCC 结果。
对象“sys.sysasymkeys”在 0 页中找到 0 行。
sys.syssqlguides的 DBCC 结果。
对象“sys.syssqlguides”在 0 页中找到 0 行。
sys.sysbinsubobjs的 DBCC 结果。
对象“sys.sysbinsubobjs”在 1 页中找到 3 行。
sys.syssoftobjrefs的 DBCC 结果。
对象“sys.syssoftobjrefs”在 1 页中找到 5 行。
sys.sqlagent_jobs的 DBCC 结果。
对象“sys.sqlagent_jobs”在 0 页中找到 0 行。
sys.sqlagent_jobsteps的 DBCC 结果。
对象“sys.sqlagent_jobsteps”在 0 页中找到 0 行。
sys.sqlagent_job_history的 DBCC 结果。
对象“sys.sqlagent_job_history”在 0 页中找到 0 行。
sys.sqlagent_jobsteps_logs的 DBCC 结果。
对象“sys.sqlagent_jobsteps_logs”在 0 页中找到 0 行。
sys.plan_persist_query_text的 DBCC 结果。
对象“sys.plan_persist_query_text”在 0 页中找到 0 行。
sys.plan_persist_query的 DBCC 结果。
对象“sys.plan_persist_query”在 0 页中找到 0 行。
sys.plan_persist_plan的 DBCC 结果。
对象“sys.plan_persist_plan”在 0 页中找到 0 行。
sys.plan_persist_runtime_stats的 DBCC 结果。
对象“sys.plan_persist_runtime_stats”在 0 页中找到 0 行。
sys.plan_persist_runtime_stats_interval的 DBCC 结果。
对象“sys.plan_persist_runtime_stats_interval”在 0 页中找到 0 行。
sys.plan_persist_context_settings的 DBCC 结果。
对象“sys.plan_persist_context_settings”在 0 页中找到 0 行。
sys.plan_persist_query_hints的 DBCC 结果。
对象“sys.plan_persist_query_hints”在 0 页中找到 0 行。
sys.plan_persist_query_template_parameterization的 DBCC 结果。
对象“sys.plan_persist_query_template_parameterization”在 0 页中找到 0 行。
sys.plan_persist_wait_stats的 DBCC 结果。
对象“sys.plan_persist_wait_stats”在 0 页中找到 0 行。
sys.persistent_version_store的 DBCC 结果。
对象“sys.persistent_version_store”在 0 页中找到 0 行。
sys.persistent_version_store_long_term的 DBCC 结果。
对象“sys.persistent_version_store_long_term”在 0 页中找到 0 行。
sys.wpr_bucket_table的 DBCC 结果。
对象“sys.wpr_bucket_table”在 0 页中找到 0 行。
node的 DBCC 结果。
对象“node”在 1 页中找到 1 行。
compute_node的 DBCC 结果。
对象“compute_node”在 1 页中找到 1 行。
distribution的 DBCC 结果。
对象“distribution”在 1 页中找到 8 行。
filegroup的 DBCC 结果。
对象“filegroup”在 1 页中找到 15 行。
database_file的 DBCC 结果。
对象“database_file”在 1 页中找到 22 行。
configuration_properties的 DBCC 结果。
对象“configuration_properties”在 11 页中找到 164 行。
version_history的 DBCC 结果。
对象“version_history”在 1 页中找到 1 行。
pdw_sp_configure的 DBCC 结果。
对象“pdw_sp_configure”在 1 页中找到 4 行。
sys.queue_messages_1977058079的 DBCC 结果。
对象“sys.queue_messages_1977058079”在 0 页中找到 0 行。
sys.queue_messages_2009058193的 DBCC 结果。
对象“sys.queue_messages_2009058193”在 0 页中找到 0 行。
sys.queue_messages_2041058307的 DBCC 结果。
对象“sys.queue_messages_2041058307”在 0 页中找到 0 行。
sys.filestream_tombstone_2073058421的 DBCC 结果。
对象“sys.filestream_tombstone_2073058421”在 0 页中找到 0 行。
sys.syscommittab的 DBCC 结果。
对象“sys.syscommittab”在 0 页中找到 0 行。
sys.filetable_updates_2105058535的 DBCC 结果。
对象“sys.filetable_updates_2105058535”在 0 页中找到 0 行。
CHECKDB 在数据库 'DWConfiguration' 中发现 0 个分配错误和 0 个一致性错误。
DBCC 执行完毕。若是 DBCC 输出了错误信息，请与系统管理员联系。
​

平常操做

可使用 SQL management studio，点击管理，新建维护计划，“检查数据库完整性”任务。能够进行一个或者多个库的操做。

建议：业务数据库， 每周作一次完整性检查。 关注检查日志，出错要即时处理

故障修复的步骤

若数据库已经出现质疑，或者已经不完整则应该进行以下操做

--一、修改数据库为紧急模式
ALTER DATABASE AdventureWorks2012 SET EMERGENCY 
--二、更改数据库为单用户模式
ALTER DATABASE AdventureWorks2012 SET SINGLE_USER
--三、修复数据库，不容许数据丢失
DBCC CheckDB（AdventureWorks2012,REPAIR_REBUILD）
--若成功 则将数据库设置为多用户模式，再改成正常状态。若不成功 还能够找到有错误的表或者视图进行修复
dbcc CHECKTABLE( table_name | view_name , REPAIR_REBUILD ) 
--若还不能成功，则能够进行容许数据库丢失的修复操做。
DBCC CheckDB（AdventureWorks2012,REPAIR_ALLOW_DATA_LOSS）
dbcc CHECKTABLE( table_name | view_name , REPAIR_ALLOW_DATA_LOSS ) 
 
--修复成功 则将数据库设置为多用户模式，再改成正常状态。若不成功 还能够找到有错误的表或者视图进行修复
--四、如果能够重启的系统，可进行服务重启
​

因为某些数据库很是巨大，若须要加快处理速度

一、DBCC CHECKDB默认使用多线程并行检查，可使用SP_CONFIGURE 'max degree of parallelism'来设置最大并行

二、使用WITH PHYSICAL_ONLY以较小开销检查数据库物理一致性

三、使用WITH NO_INDEX来避免检查索引

四、也能够将CHECKDB拆分红多个步骤完成，CHECKFILEGROUP CHECKALLOC CHECKTABLE

五、在进行REPAIR_ALLOW_DATA_LOSS 操做前先备份数据库。

进阶

详细看下进行修复的几个选项的区别，有助于你进行操做时候的选择。

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

REPAIR_ALLOW_DATA_LOSS

尝试修复报告的全部错误。 这些修复可能会致使一些数据丢失，REPAIR_ALLOW_DATA_LOSS 选项是受支持的功能，可是，它可能并不是老是使数据库处于物理上一致的状态的最佳选项。 若是成功，REPAIR_ALLOW_DATA_LOSS 选项可能会致使一些数据丢失。 实际上，它可能致使的数据丢失多于用户从上次已知成功备份还原数据库致使的数据丢失。 Microsoft 始终建议用户将从上次已知成功备份还原做为从由 DBCC CHECKDB 报告的错误恢复的主要方法。 REPAIR_ALLOW_DATA_LOSS 选项不是从已知成功备份还原的替代方法。 这是一个紧急选项，仅当不可从备份恢复时建议做为“最后手段”使用。

仅能使用 REPAIR_ALLOW_DATA_LOSS 选项修复的某些错误可能涉及释放行、页或一些列页以清除错误。 用户不可再访问或恢复已释放的数据，且没法肯定已释放数据的准确内容。 所以，释听任何行或页后参照完整性可能不许确，由于此修复操做不包括检查或维护外键约束。 使用 REPAIR_ALLOW_DATA_LOSS 选项后，用户必须检查其数据库的参考完整性（使用 DBCC CHECKCONSTRAINTS）。

在执行修复以前，请建立属于此数据库的文件的物理副本。 这包括主数据文件 (.mdf)、任意辅助数据文件 (.ndf)、全部事务日志文件 (.ldf) 和构成数据库的其余容器，包括全文目录、文件流文件夹、内存优化数据等。 在执行修复前，请考虑将数据库的状态更改成紧急模式，并尝试从关键表中提取尽量多的信息并保存这些数据。

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

REPAIR_REBUILD执行不会丢失数据的修复。 这包括快速修复（如修复非汇集索引中缺乏的行）以及更耗时的修复（如从新生成索引）。此参数不修复涉及 FILESTREAM 数据的错误。

数据库处于紧急模式而且以 REPAIR_ALLOW_DATA_LOSS 子句运行 DBCC CHECKDB 时，将执行如下操做：

  • DBCC CHECKDB 将使用因为 I/O 或校验和错误而标记为不可访问的页，就如同这些错误没有出现过同样。 这样操做将增长从数据库恢复数据的机会。   

  • DBCC CHECKDB 将尝试使用常规的基于日志的恢复方法恢复数据库。   

  • 若是因为事务日志损坏而致使数据库恢复失败，则将从新生成事务日志。 从新生成事务日志可能致使事务一致性丢失。

另外若是作了复制的数据库，则会更麻烦

运行具备 REPAIR_ALLOW_DATA_LOSS 选项的 DBCC CHECKDB 命令可能会影响用户数据库（发布数据库和订阅数据库）以及由复制使用的分发数据库。 发布数据库和订阅数据库包括已发布的表和复制元数据表。 请注意这些数据库中的下列潜在问题：

  • 已发布的表。 可能不会复制由 CHECKDB 进程为修复损坏的用户数据而执行的操做：   

  • 合并复制使用触发器跟踪对已发布的表所作的更改。 若是 CHECKDB 进程插入、更新或删除了行，则触发器不会激发；所以，更改将不会复制。

  • 事务复制使用事务日志跟踪对已发布的表所作的更改。 而后，日志读取器代理将这些更改移动到分发数据库。 某些 DBCC 修复即便记入日志，仍然没法由日志读取器代理复制。 例如，若是数据页由 CHECKDB 进程释放，则日志读取器代理不会将它翻译为 DELETE 语句；所以，更改将不会复制。

  • 复制元数据表。 由 CHECKDB 进程为修复损坏的复制元数据表而执行的操做须要删除并从新配置复制。   

若是必须对用户数据库或分发数据库运行具备 REPAIR_ALLOW_DATA_LOSS 选项的 DBCC CHECKDB 命令：

  1. 中止系统：中止数据库和复制拓扑中全部其余数据库的活动，而后尝试同步全部节点。 有关详细信息，请参阅中止复制拓扑（复制 Transact-SQL 编程）。   

  2. 执行 DBCC CHECKDB。   

  3. 若是 DBCC CHECKDB 报表包括分发数据库中任何表或用户数据库中任何复制元数据表的修复，则请删除并从新配置复制。 有关详细信息，请参阅禁用发布和分发。   

  4. 若是 DBCC CHECKDB 报表包括任何已复制表的修复，则请执行数据验证以肯定发布数据库和订阅数据库中的数据之间是否存在差别。

    后记

    数据库运维工做历来都不能偷懒，你不知道何时会发生什么 。 只有作足了工做，才会及早的处理好潜在的故障。 数据原本无价，只由于你付出更多，才能保证完整！

    看起来很简单的数据库完整性问题，倒是有可能某天发生在你的数据库上。

联系我们 沪ICP备2021010478号-7