SQL Server日志损坏造成整个数据库损坏的修复 (2).docx

SQL Server 日志损坏造成整个数据库损坏的修复版本：V1.0作者：知行合一邮箱：409629442时间：2014/9/29一、 问题说明 由于用户的数据库某张表比较大，大约有1000万条记录，数据库管理员在非业务时间对这个表进行删除清理，删除操作持续了1个小时左右，到工作时间，仍然没有正常结束。前端用户反应应用系统比较慢后，数据库管理员对删除操作进行终止，终止时仍然无法正常终止。最后，数据库管理员不得已对数据库进行重启，重启后发现数据库已经无法正常打开。由于用户比较急，就用一个比较老的备份进行了恢复。我们赶赴现场后，原先的数据库已经被删除，但用户已经针对原先的数据库文件和日志文件进行了拷贝。我们把备份的数据库文件拷贝到异机进行附加，总是报错，提示无法读取日志文件。具体报错如下：The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.Msg 1813, Level 16, State 2, Line 2通过上面的报错可知，数据库的日志文件发生了损坏，这时已经不能通过简单的附加方式进行恢复，也无法通过无日志附加的方式进行附加，因为这时数据库处在一个不一致的状态。二、 环境介绍操作系统：Windows 2008 R2SQL server: SQL Server 2008 SP1数据文件路径：数据文件路径File_id逻辑文件名称文件组大小D:PPmonitor PPmonitor_Data.MDF1PPmonitor_DataPRIMARY41787200KBD:PPmonitorPPmonitor_Log.LDF2PPmonitor_LogD:PPmonitorPPmonitor_Data1.NDF3PPmonitor_1_DataPRIMARYD:PPmonitorPPmonitor_Log1.ldf4PPmonitor_1_LogD:PPmonitorPPmonitor_Log2.ldf5PPmonitor_2_LogG:PPmonitorPPmonitor_Dtat3.Mdf6PPmonitor_3_DataPRIMARYG:PPmonitorPPmonitor_data2.Ndf7PPmonitor_2_DataPRIMARYG:PPmonitorPPmonitor_Log3.Ldf8PPmonitor_3_Log注：数据文件后面为数据文件的file_id,file_id 可以通过sys.master_files进行查看三、处理过程 由于这时数据库已经无法正常打开（已经没有对应的数据库，或数据库状态错误，查看不了任何属性信息），所以我们必须重新创建同名的数据库，然后用备份的数据文件覆盖新创建的数据文件。处理的主要过程如下：3.1、新建同名的数据库。1、新建数据库时，要求数据文件目录、路径、数据文件大小要和原来保持一致。如果是恢复到其他机器，要保证SQL Server实例的版本和原来保持一致，小版本也要保持一致，如果小版本不一致，需要设置实例和数据库兼容级别。2、数据文件大小不能小于原来的数据文件，可以比原来的数据文件大。数据文件大小不一致时，替换数据文件时会发生错误。3、日志文件大小可以不一致。4、需要保持文件的file_id 要和原来对应，文件的Id 可以通过mster数据库系统表sys.master_files进行查看。如果包含文件组，且文件组中含有多个数据文件，直接一起创建的时候，可能会产生文件id和原来不一致的情况，这时可以采用一个一个数据文件单独创建的方式进行创建。如果文件id 不一致，用备份的数据文件替换新建的数据文件时会报错。具体错误信息如下：文件Id 的查看方法：Use masterSelect * from sys.master_file;3.2、用备份的数据文件替换新的数据文件1、首先停止SQL Server服务2、替换数据文件和日志文件3、启动数据库服务如果替换时，报拒绝访问错误，需要把数据文件的权限更改为everyone 完全访问3.3、数据库日志修复在用备份的数据和日志文件替换新建的数据文件和日志文件后，启动数据库时，数据库自动置为in recovery 状态，这时修复的时间可能会比较长。 由于日志文件3已经发生损坏，修复到损坏日志部分的时候，数据库会无法进行修复。这时可以采用如下方法处理：sp_configure allow updates, 1reconfigure with overrideGo3.4、损坏的数据库日志重建1、停止数据库服务2、删除日志文件3的数据文件3、启动数据库4、查询数据库信息select name,filename from sys.sysaltfiles where dbid=5;select * from sys.master_files;5、重建损坏的日志文件如果数据库没有处在质疑模式，可能需要手工把数据库设置为质疑模式：sp_configure allow updates, 0reconfigure with overrideGoALTER DATABASE PPmonitor SET EMERGENCYGOALTER DATABASE PPmonitor SET SINGLE_USER;alter database PPmonitor rebuild log on(Name=PPmonitor_3_Log,filename=G:PPmonitorPPmonitor_Log3.Ldf);6、alter database PPmonitor set online;7、alter database PPmonitor set multi_usergo这时数据库中的表已经能够正常访问，由于某些表仍然存在一致性问题，在访问时会报错，可能的报错信息如下：3.5、数据库的一致性修复1、把数据库置为紧急和单用户模式reconfigure with overrideGoALTER DATABASE PPmonitor SET EMERGENCYGOALTER DATABASE PPmonitor SET SINGLE_USER;2、修复数据库中存在的错误DBCC CheckDB (PPmonitor, REPAIR_ALLOW_DATA_LOSS)修复完成后，有部分报错，具体如下：sg 259, Level 16, State 1, Line 1Ad hoc updates to system catalogs are not allowed.Msg 2510, Level 16, State 17, Line 1DBCC checkdb error: This system table index cannot be recreated.DBCC results for PPmonitor.Repair: The page (1:3960704) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594284408832 (type Unknown).Repair: The page (1:4088798) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594284408832 (type Unknown).Repair: The page (1:4089144) has been deallocated from object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594284408832 (type Unknown).CHECKDB found 12638 allocation errors and 0 consistency errors in table BarcodeReslut (object ID 1966682104).CHECKDB fixed 12638 allocation errors and 0 consistency errors in table BarcodeReslut (object ID 1966682104).Repair: IAM chain for object ID 2000166321, index ID 1, partition ID 72057594193575936, alloc unit ID 72057594196459520 (type In-row data), has been truncated before page (1:210) and will be rebuilt.Repair: The extent (6:448) has been allocated to object ID 2000166321, index ID 1, partition ID 72057594193575936, alloc unit ID 72057594196459520 (type In-row data).Repair: Page (7:4613336) next and (0:0) previous pointers have been set to match each other in object ID 2000166321, index ID 1, partition ID 72057594193575936, alloc unit ID 72057594196459520 (type In-row data).Msg 2575, Level 16, State 1, Line 1The Index Allocation Map (IAM) page (1:210) is pointed to by the next pointer of IAM page (7:4613336) in object ID 2000166321, index ID 1, partition ID 72057594193575936, alloc unit ID 72057594196459520 (type In-row data), but it was not detected in the scan. The error has been repaired.CHECKDB found 1 allocation errors and 0 consistency errors in table TestVoltage (object ID 2000166321).CHECKDB fixed 1 allocation errors and 0 consistency errors in table TestVoltage (object ID 2000166321).Msg 8913, Level 16, State 3, Line 1Extent (7:616) is allocated to dbo.ZModul and at least one other object. The error has been repaired.CHECKDB found 1 allocation errors and 0 consistency errors in table ZModul (object ID 2121110647).CHECKDB fixed 1 allocation errors and 0 consistency errors in table ZModul (object ID 2121110647).CHECKDB found 470285 allocation errors and 9 consistency errors in database PPmonitor.CHECKDB fixed 470259 allocation errors and 8 consistency errors in database PPmonitor.repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PPmonitor, repair_allow_data_loss).DBCC execution completed. If DBCC printed error messages, contact your system administrator.3、表分配检查USE PPmonitor;DBCC CheckAlloc();DBCC results for PPmonitor.Msg 8904, Level 16, State 1, Line 2Extent (6:525720) in database ID 5 is allocated by more than one allocation object.Msg 8913, Level 16, State 1, Line 2Extent (6:525720) is allocated to GAM and at least one other object.Msg 8904, Level 16, State 1, Line 2Extent (6:582336) in database ID 5 is allocated by more than one allocation object.Msg 8913, Level 16, State 1, Line 2Extent (6:582336) is allocated to GAM and at least one other object.Msg 8904, Level 16, State 1, Line 2Extent (6:606600) in database ID 5 is allocated by more than one allocation object.Msg 8913, Level 16, State 1, Line 2Extent (6:606600) is allocated to GAM and at least one other object.data), index extents 2089, pages 16491, mixed extent pages 6. Object ID 2121110647, index ID 10, partition ID 72057594202947584, alloc unit ID 72057594205765632 (type In-row data), index extents 945, pages 7403, mixed extent pages 15. Object ID 2138594807, index ID 1, partition ID 72057594173194240, alloc unit ID 72057594175946752 (type In-row data), data extents 0, pages 3, mixed extent pages 3.The total number of extents = 1938384, used pages = 15430706, and reserved pages = 15507106 in this database. (number of mixed extents = 702, mixed pages = 5650) in this database.CHECKALLOC found 58 allocation errors and 0 consistency errors in database PPmonitor.DBCC execution completed. If DBCC printed error messages, contact your system administrator.4、查询测试查询指定条件时报错，只能全部按顺序扫描，即只能使用select * from table使用select count(*) from table 报错，具体报错如下：Attempt to fetch logical page (1:229734) in database 5 failed. It belongs to allocation unit 72057594251182080 not to 72057594275561472.消息 605，级别 21，状态 3，第 2 行尝试在数据库 5 中提取逻辑页 (1:1824) 失败。该逻辑页属于分配单元 72057594194165760，而非 72057594237091840。5、表无损修复把数据启动到单用户模式下ALTER DATABASE PPmonitor SET single_user;GO对出错的表进行修复DBCC CheckTable(AdjustData_7878_N, REPAIR_REBUILD);AdjustData_7878_N的 DBCC 结果。修复: 已为数据库 PPmonitor 中的对象 dbo.AdjustData_7878_N 成功地重新生成了 Clustered 索引。消息 8945，级别 16，状态 1，第 1 行表错误: 将重新生成对象 ID 368876531，索引 ID 1。 该错误已修复。消息 8976，级别 16，状态 1，第 1 行表错误: 对象 ID 368876531，索引 ID 1，分区 ID 72057594241220608，分配单元 ID 72057594244825088 (类型为 In-row data)。在扫描过程中未发现页 (3:656)，但该页的父级 (7:4810173) 和上一页 (7:4850791) 都引用了它。请检查以前的错误消息。 该错误已修复。消息 8980，级别 16，状态 1，第 1 行表错误: 对象 ID 368876531，索引 ID 1，分区 ID 72057594241220608，分配单元 ID 72057594244825088 (类型为 In-row data)。索引节点页 (7:4810173)，槽 617 指向子页 (3:657) 和上一子页 (3:656)，但未遇到这些页。 该错误已修复。消息 8980，级别 16，状态 1，第 1 行表错误: 对象 ID 368876531，索引 ID 1，分区 ID 72057594241220608，分配单元 ID 72057594244825088 (类型为 In-row data)。索引节点页 (7:4810173)，槽 618 指向子页 (3:658) 和上一子页 (3:657)，但未遇到这些页。 该错误已修复。消息 8980，级别 16，状态 1，第 1 行表错误: 对象 ID 368876531，索引 ID 1，分区 ID 72057594241220608，分配单元 ID 72057594244825088 (类型为 In-row data)。索引节点页 (7:4810173)，槽 619 指向子页 (3:659) 和上一子页 (3:658)，但未遇到这些页。 该错误已修复。消息 8980，级别 16，状态 1，第 1 行表错误: 对象 ID 368876531，索引 ID 1，分区 ID 72057594241220608，分配单元 ID 72057594244825088 (类型为 In-row data)。索引节点页 (7:4810173)，槽 620 指向子页 (3:660) 和上一子页 (3:659)，但未遇到这些页。 该错误已修复。对象 AdjustData_7878_N 的 59527 页中有 344840 行。CHECKTABLE 在表 AdjustData_7878_N (对象 ID 368876531)中发现 0 个分配错误和 5 个一致性错误。CHECKTABLE 在表 AdjustData_7878_N (对象 ID 368876531)中修复了 0 个分配错误和 5 个一致性错误。DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。查询表的分配单元可以通过如下语句：SELECT au.allocation_unit_id, OBJECT_NAME(p.object_id) AS table_name, AS filegroup_name,au.type_desc AS allocation_type, au.data_pages, partition_numberFROM sys.allocation_units AS auJOIN sys.partitions AS p ON au.container_id = p