SQL Server 2012实施与管理实战指南摘要 - 第 10 章

第10章 数据库损坏修复 409 

10.1.               常见错误解读 

10.1.1.                    823 - 硬件问题，或者数据文件有严重损坏

从错误产生的机制可以看出，823错误是发出一个页面读写请求时发生的，和读写的内容没有关系。所以823错误和SQL Server本身无关。通常是物理文件损坏导致此错误，但也可能是设备驱动程序导致的。如果某个数据文件上反复出现823错误，要不就是硬件设备出了问题，要不就是数据文件已经发生了非常严重的损坏。这个错误基本上意味着数据页里的有效数据已经丢失，一般DBCC CHECKDB很难修复。

10.1.2.                    824 - 发生在页面这一级的逻辑错误，但是损坏的来源，大都不是SQL Server自己，基本上还是在I/O子系统。由于SQL Server的读写请求是发给Windows，再由Windows发给底层的磁盘系统的，所以问题有可能发生在Windows以下的每一层，例如磁盘驱动器存在故障、磁盘固件存在问题、设备驱动程序不正确等。很多时候DBCC CHECKDB能够修复逻辑一些错误。但如果页面里面存储的数据在824错误发生之前就已经丢失，SQL Server无法将它们修复回来。所以824错误往往意味着部分数据丢失。

     =========================延伸阅读 on Checksum vs. Torn Page Detection======================

You might wonder why the switch from Torn Page Detection to Checksums, and what’s the difference.  The difference is in how they validate IO.  When it writes a page, Torn Page Detection grabs the first 2 bits of every 512 byte sector on each page and stores those bits in the page header.  When the page is later read back in from disk, SQL Server compares those header bits with the bits from the sector, to make sure they’re still the same.  Now, that check is better than nothing, but you can see where it might potentially miss corruption in the rest of the sector, right?

The Checksum option, on the other hand, creates a checksum value using the content of the entire page, and saves that value in the header.  When a page is read from disk, a checksum is created again and compared to the saved checksum.  Since any difference in the bytes read for that page will result in a different checksum value, this is a far more thorough method for validating IO.

10.1.3.                    605 - 据库中的页或分配已损坏。严重级别为21表示可能存在数据损坏。造成的原因包括损坏的页链、损坏的IAM或该对象的sys.objects目录视图中存在无效条目。这些错误通常由硬件或磁盘设备驱动程序故障而引起。对这些损坏，一般用DBCC CHECKDB也很难修复。但严重级别为12表示可能存在暂时性错误，即在缓存中出现错误，但不表示对磁盘上的数据造成破坏。无需恢复，稍后重新运行查询即可。

10.1.4.                    其它

·               8946 - invalid PFS_PAGE （PFS页面头有损坏）

·               7985/8966 - Could not read and latch page (系统表上的聚集索引页面上有损坏)

·               2570 - Column "c1" value is out of range for data type "datetime".(某个字段的值不符合字段数据类型定义)

·               3854 - Attribute xxx of row xxx in sys.sql_dependencies has a matching row in sys.objects (type=SN) that is invalid (元数据有损坏)

·               遇到这些错误，可以用DBCC CHECKDB命令来检查和修复。有些错误是可以不丢数据就能修复的，有些是要丢数据才能修复物理层面错误的，有些是即使丢数据也没办法修复的。That is, DBCC CHECKDB can't fix everything. You may end up having to perform manual and time-consuming data extraction from the corrupt database and losing lots of data because of, say, a critical system table corruption. Bottom line (as usual) - make sure you have valid backups so you don't get into this state! 

10.2.               dbcc checkdb 414 

10.2.1.                    dbcc checkdb在做什么 414 

·               检查数据库里有没有损坏发生。

·               尽力修复数据库损坏

10.2.2.                    dbcc checkdb提供的修复方法 418 -

·               默认DBCC CHECKDB只会验证数据库是否完好，不会主动去做数据库修复动作。要尝试修复数据库，需要将数据库设成单用户模式，才能使用以下三个修复选项之一。

o        REPAIR_ALLOW_DATA_LOSS - 尝试修复报告的所有错误。

o        REPAIR_FAST - 保留该语法只是为了向后兼容。未执行任何修复操作，请不要使用。

o        REPAIR_REBUILD - 执行次要、快速修复（例如，修复非聚集索引中的额外键）及耗时修复（例如，重新生成索引）。执行这些修复时不会有丢失数据的危险。换句话说，对那些有丢失数据危险的错误，无法用此选择。原文如下：

REPAIR_REBUILD - Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index. 

REPAIR_REBUILD does not repair errors involving FILESTREAM data. 

·               REPAIR_ALLOW_DATA_LOSS到底做些什么？

o        将由于I/O或校验错误而被标记为不可访问的页重新标记为可访问，就如同这些错误没有出现过一样。这样用户将能够访问这些页面，虽然页面里的内容肯定有问题。

o        将尝试使用常规的基于日志的恢复技术恢复数据库。

o        如果由于事务日志损坏而导致数据库恢复失败，则将重建事务日志。但是重建事务日志可能会导致数据库里的事务不一致

·               修复成功了是不是错误就不存在？- NO, NO, NO!

o        修复成功, 仅说明数据库在物理结构上是正确的，数据库ONLINE，用户可以正常访问。但是，

o        数据库可能包含一种或多种事务不一致的情况，

o        修复操作也不会考虑表本身或表之间可能存在的任何约束。如果指定的表与一个或多个约束有关，建议在修复操作后运行DBCC CHECKCONSTRAINTS (to identify any business logic flaws), （看看有没有错误，如有，就要Repair）。如果必须使用REPAIR，则运行不带有修复选项的DBCC CHECKDB来查找要使用的修复级别（不是查不出来吗？）。如果必须使用REPAIR_ALLOW_DATA_LOSS级别（就2个级别，REPAIR_REBUILD和REPAIR_ALLOW_DATA_LOSS），则建议在运行带有此选项的DBCC CHECKDB之前备份数据库。原文：

Repair operations do not consider any of the constraints that may exist on or between tables. If the specified table is involved in one or more constraints, we recommend running DBCC CHECKCONSTRAINTS after a repair operation. If you must use REPAIR, run DBCC CHECKDB without a repair option to find the repair level to use. If you use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKDB with this option.

o        REPAIR_ALLOW_DATA_LOSS中data loss不仅仅是指一些数据记录可能被删除，还指有些错误的记录会保留在数据库里，所以这是一种比较危险的选择，只能在万不得已的时候选用。

o        记住，运行REPAIR_ALLOW_DATA_LOSS之前，先备份数据库，因为修复不一定成功，成功了，也未必会更好。

·               如果一个数据库使用REPAIR_ALLOW_DATA_LOSS级别都不能修复，咋办?

o        按照预先的备份恢复策略，恢复数据库备份-最好的办法。

o        如果损坏发生在某些用户对象上（用户表、视图、存储过程等），可以把它们DROP掉试试。

o        将数据库设成紧急只读模式，用“SELECT ... INTO”或其它方式，将数据导入到一个新建的空数据库里。虽然这种方法能够从数据库中将所有能读出来的数据都读出来，挽救尽可能多的数据。但是损坏严重程度不一样，丢的数据多少也不一样。这样救回来的数据库各个数据表的状态将会不一致，一般在逻辑上会有很大的问题。

10.2.3.                    如何在超大数据库上运行dbcc checkdb 420 

·               在执行DBCC CHECKDB命令时，数据库引擎创建了一个数据库快照， DBCC命令对该快照运行检查，而不是对数据库本身做检查。一般不需要更改

·               默认情况下，DBCC CHECKTABLE对对象执行多个线程并行检查，以加快检查速度。但其它同时在运行的连接可能就拿不到足够的资源。通过使用跟踪标志2528，可以禁用并行检查。若要限制DBCC检查可使用的处理器的最大数目，可使用sp_configure改变max degree of parallelism选项。

·               使用PHYSICAL_ONLY选项可以极大地缩短对大型数据库运行DBCC CHECKDB的时间。因此，针对生产系统中频繁使用的情况，建议使用该选项。

·               Recommendations

o        尽量使用分区表（partitioned table）机制

o        如果数据库没有分区机制，可以把数据库里面的所有表格按照它们占用的页面数量大致分为7组，每组的页面数目大致一样。然后按照这样的方式把CHECKDB里面的关键任务分散在每天运行 (see http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-consistency-checking-options-for-a-vldb/)

ü      周一到周三：每天运行一组DBCC CHECKTABLE。

ü      周四：DBCC CHECKALLOC + 一组DBCC CHECKTABLE。

ü      周五周六：每天运行一组DBCC CHECKTABLE。

ü      周日：DBCC CHECKALLOC + DBCC CHECKCATALOG + 一组DBCC CHECKTABLE。

o        如果您有一台备用服务器，则可以考虑先不在生产环境上运行DBCC CHECKDB。而是把生产环境的数据库通过备份方式，还原到备用服务器上，在备用服务器运行DBCC CHECKDB。如果备用服务器上执行的结果显示数据完整性有问题，那么则一定要在生产环境再次运行DBCC CHECKDB，因为可能是生产环境的数据本来就有问题，也有可能是还原到备用服务器的过程中出了问题。

·               Anyway to estimate how long it takes to finish the command?

--Run this script in a different window from that running DBCC CHECKDB

SELECT  session_id ,

        request_id ,

        percent_complete ,

        estimated_completion_time ,

        DATEADD(ms, estimated_completion_time, GETDATE()) AS EstimatedEndTime ,

        start_time ,

        status ,

        command

FROM    sys.dm_exec_requests

WHERE   command LIKE '%DBCC%' 

10.3.               不同部位损坏的应对 423 

10.3.1.                    备份文件损坏 424 

·               可以使用WITH CONTINUE_AFTER_ERROR参数尝试恢复

RESTORE DATABASE TESTDB FROM DISK='D:\temp\TESTDB_BAD.bak'

WITH CONTINUE_AFTER_ERROR

·               不幸的是，对数据库做完恢复后，该数据库有可能处于Suspect(质疑)状态。我们还是无法使用该数据库。 我们可以把该数据库设置为紧急状态，并执行数据库修复命令REPAIR_ALLOW_DATA_LOSS:

ALTER DATABASE TESTDB SET EMERGENCY 

ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO 

DBCC CHECKDB(TestDB,REPAIR_ALLOW_DATA_LOSS)

GO 

ALTER DATABASE TestDB SET MULTI_USER

GO 

USE TESTDB 

SELECT  *

FROM    TESTTABLE 

·               如果还是不行，这备份就用不起来了。

10.3.2.                    日志文件损坏 426 

·               日志文件损坏后，数据库可能会处于RECOVERY_PENDING状态。

·               Rebuild the log

ALTER DATABASE TESTDB SET EMERGENCY 

ALTER DATABASE TESTDB Rebuild LOG ON (name=xxxx_log, filename='D:\temp\xxxx_log.LDF') 

ALTER DATABASE TESTDB SET MULTI_USER 

--need to run DBCC CHECKDB after the rebuilding the log 

·               However, even if it is successful, some data may not have the desirable values. The data consistency cannot be guaranteed. It’s better to use backup/restore.

10.3.3.                    用户数据文件损坏 427 

·               如果Errorlog提示用户数据在非聚焦索引页上有错误，可以drop it and create a new one, 数据不会丢失。

DROP INDEX idx2 ON TESTTABLE

GO 

CREATE INDEX idx2 ON TESTTABLE(NAME) 

·               如果Errorlog提示用户数据在聚焦索引页上有错误，可以用REPAIR_ALLOW_DATA_LOSS的方法对数据库进行修复， 但数据会有丢失。这是因为修复程序发现页内数据有问题，采用了比较保守的办法，把整个页面的数据删除，以维护数据的一致性。

ALTER DATABASE TESTDB SET EMGERGENCY 

ALTER DATABASE TESTDB SET SINGLE_USER withrollback IMMEDIATE

GO 

DBCC CHECKDB(TESTDB, 'REPAIR_ALLOW_DATA_LOSS')

GO 

ALTER DATABASE TESTDB SET MULTI_USER 

·               数据库有一些系统页面或系统表格损坏如PFS页面，GAM页面和SGAM页面。用REPAIR_ALLOW_DATA_LOSS的方法对数据库进行修复很难成功。只能从备份中恢复。

10.3.4.                    系统数据库损坏 431 

·               对待tempdb数据库的损坏，可以通过重新启动对其进行恢复。

·               对待master，MSDB和MODEL数据库的损坏。

o        备份法 - 从备份中恢复系统数据库是最好的办法。

o        拷贝+重建法 - 如果没有备份，那么修复系统数据库的方法，则是从相同版本的数据库上，拷贝系统数据库的数据文件和日志文件，然后替换受损的系统数据库。这样，损失的数据，包括master里面的数据和MSDB里面的数据。用户可以通过重建登录以及重建Job来进行恢复（这方法行吗？（1）除了登录和Job，还有其它信息，（2）这些信息有备份吗？至少要有script才行。重建也面临相同的问题。）

10.4.               如何从损坏的数据库导出数据 432 

10.4.1.                    要点

·               有时候导数据可能比直接DBCC CHECKDB能挽回更多数据。但使用这种方法得到的数据的正确性难以保证，因为那时数据库已经有一致性问题了。

·               当查询到有问题的数据页的时候，SQLSERVER会报I/O一致性错误，所以导数据不一定行得通。可以考虑使用TOP [number] 的方式或者加where的查询，尽量导出。

·               碰到数据受损严重，那么扫描是无法进行下去的，因而无法使用导出法，这时不得不用其它方法如REPAIR_ALLOW_DATA_LOSS，或者对数据库从备份中还原。

10.4.2.                    导出的具体做法

·               在新库中建立与Source Tables一样的表

·               用Insert into NewTable Select * From OldTable (for all tables)

o        必要的话，加Top或Where

o        必要的话，SET IDENTITY_INSERT NewTable ON

o        必要的话，建立与Source Tables一样的Indexes和FKs

10.5.               如何使用tablediff工具发现哪些数据有丢失或改变 439 

10.5.1.                    可将比较的结果输出到一个结果表中

10.5.2.                    如果表中有BLOBs, 可以排除它们，建立View，然后比较视图。

10.6.               数据库反复损坏问题应对 440 

10.6.1.                    sql i/o 管理操作方法 441

·               Buffer Pool and dirty pages

·               Three ways to write dirty pages to Disks

o        Lazy writing

o        Eager writing – no log records such as bulk insert.

o        Check point

10.6.2.                    确保系统i/o正常 443 

·               95%以上的数据库反复损坏的问题都是由I/O的子系统导致的， 例如Windows， 各级控制器，各级驱动程序，电缆等硬件组件，磁盘的光纤通道，硬盘子系统自己的读写缓存区，甚至内存等等。

·               SQL Server本身不大会导致数据库反复损坏。

·               如何确保系统i/o正常

o        系统健康检查

ü      有无非正常关机或蓝屏 – 看Windows System Events Log

ü      有无硬件错误 – 看Windows System Events Log

ü      有无病毒攻击

ü      是否由防病毒软件引起，如是，暂时关掉Anti-Virus

o        I/O子系统验证

ü      电源掉电测试

ü      SQLIOSim测试

o        安装软件和各种固件的升级补丁。

10.6.3.                    sql层面能够做的设置 44 – For 检测，不是防止

·               Torn Page Detection – 电源故障保护

·               Checksum

·               内存页面被篡改–DBCC TRACEON (815, -1), DBCC TRACEONE(831, -1)

·               Stale Read (SQL Server要求将某个页面写入硬盘，I/O子系统报告写入已经完成，但SQL Server下次读取时，读到的还是写入前的内容) – 用T818开关跟踪或使用DBCC TRACEON (818, -1) -- for diagnosing the possible stale read/lost write symptoms.。

10.7.               database mirroring和alwayson的页面自动修复功能 449 

10.7.1.                    DB Mirroring – 只对823, 824,829之类的错误能自动修复。

Database mirroring automatic page repair tries to repair only pages in a data file on which an operation has failed for one of the errors listed in the following table.

Error number	Description	Instances that cause automatic page-repair attempt
823	Action is taken only if the operating system performed a cyclic redundancy check (CRC) that failed on the data.	ERROR_CRC. The operating-system value for this error is 23.
824	Logical errors.	Logical data errors, such as torn write or bad page checksum.
829	A page has been marked as restore pending.	All.

10.7.2.                    AlwaysOn

·               与Database Mirroring基本一样，也是自动修复823, 824,829之类的错误

·               查看：sys.dm_hadr_auto_page_repair,修复了一页就会多一条记录。

10.8.               小结 452 

·               首选方案是恢复备份

·               不太严重，尝试无损恢复（Repair_Rebuild）或重建Index

·               对日志受损，可以考虑重建日志

·               对数据受损可以尝试Repair_Allow_Data_Loss

·               数据库如还能访问，试试导出、导入

·               检查磁盘或更换磁盘

·               部署HA方案如DB Mirroring或AlwaysOn