SQL Server日志損壞造成整個(gè)數(shù)據(jù)庫(kù)損壞的修復(fù)

1、SQL Server 日志損壞造成整個(gè)數(shù)據(jù)庫(kù)損壞的修復(fù)版本：V1.0作者：知行合一郵箱：409629442時(shí)間：2014/9/29一、 問(wèn)題說(shuō)明 由于用戶的數(shù)據(jù)庫(kù)某張表比較大，大約有1000萬(wàn)條記錄，數(shù)據(jù)庫(kù)管理員在非業(yè)務(wù)時(shí)間對(duì)這個(gè)表進(jìn)行刪除清理，刪除操作持續(xù)了1個(gè)小時(shí)左右，到工作時(shí)間，仍然沒(méi)有正常結(jié)束。前端用戶反應(yīng)應(yīng)用系統(tǒng)比較慢后，數(shù)據(jù)庫(kù)管理員對(duì)刪除操作進(jìn)行終止，終止時(shí)仍然無(wú)法正常終止。最后，數(shù)據(jù)庫(kù)管理員不得已對(duì)數(shù)據(jù)庫(kù)進(jìn)行重啟，重啟后發(fā)現(xiàn)數(shù)據(jù)庫(kù)已經(jīng)無(wú)法正常打開(kāi)。由于用戶比較急，就用一個(gè)比較老的備份進(jìn)行了恢復(fù)。我們趕赴現(xiàn)場(chǎng)后，原先的數(shù)據(jù)庫(kù)已經(jīng)被刪除，但用戶已經(jīng)針對(duì)原先的數(shù)據(jù)庫(kù)文件和日志文件進(jìn)行了

2、拷貝。我們把備份的數(shù)據(jù)庫(kù)文件拷貝到異機(jī)進(jìn)行附加，總是報(bào)錯(cuò)，提示無(wú)法讀取日志文件。具體報(bào)錯(cuò)如下：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 d

3、ue to a hardware or environment failure.Msg 1813, Level 16, State 2, Line 2通過(guò)上面的報(bào)錯(cuò)可知，數(shù)據(jù)庫(kù)的日志文件發(fā)生了損壞，這時(shí)已經(jīng)不能通過(guò)簡(jiǎn)單的附加方式進(jìn)行恢復(fù)，也無(wú)法通過(guò)無(wú)日志附加的方式進(jìn)行附加，因?yàn)檫@時(shí)數(shù)據(jù)庫(kù)處在一個(gè)不一致的狀態(tài)。二、 環(huán)境介紹操作系統(tǒng)：Windows 2008 R2SQL server: SQL Server 2008 SP1數(shù)據(jù)文件路徑：數(shù)據(jù)文件路徑File_id邏輯文件名稱文件組大小D:PPmonitor PPmonitor_Data.MDF1PPmonitor_DataPRIMARY4178

4、7200KBD: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_DataPRIMARY

5、G:PPmonitorPPmonitor_Log3.Ldf8PPmonitor_3_Log注：數(shù)據(jù)文件后面為數(shù)據(jù)文件的file_id,file_id 可以通過(guò)sys.master_files進(jìn)行查看三、處理過(guò)程 由于這時(shí)數(shù)據(jù)庫(kù)已經(jīng)無(wú)法正常打開(kāi)（已經(jīng)沒(méi)有對(duì)應(yīng)的數(shù)據(jù)庫(kù)，或數(shù)據(jù)庫(kù)狀態(tài)錯(cuò)誤，查看不了任何屬性信息），所以我們必須重新創(chuàng)建同名的數(shù)據(jù)庫(kù)，然后用備份的數(shù)據(jù)文件覆蓋新創(chuàng)建的數(shù)據(jù)文件。處理的主要過(guò)程如下：3.1、新建同名的數(shù)據(jù)庫(kù)。1、新建數(shù)據(jù)庫(kù)時(shí)，要求數(shù)據(jù)文件目錄、路徑、數(shù)據(jù)文件大小要和原來(lái)保持一致。如果是恢復(fù)到其他機(jī)器，要保證SQL Server實(shí)例的版本和原來(lái)保持一致，小版本也要保持一致，如果

6、小版本不一致，需要設(shè)置實(shí)例和數(shù)據(jù)庫(kù)兼容級(jí)別。2、數(shù)據(jù)文件大小不能小于原來(lái)的數(shù)據(jù)文件，可以比原來(lái)的數(shù)據(jù)文件大。數(shù)據(jù)文件大小不一致時(shí)，替換數(shù)據(jù)文件時(shí)會(huì)發(fā)生錯(cuò)誤。3、日志文件大小可以不一致。4、需要保持文件的file_id 要和原來(lái)對(duì)應(yīng)，文件的Id 可以通過(guò)mster數(shù)據(jù)庫(kù)系統(tǒng)表sys.master_files進(jìn)行查看。如果包含文件組，且文件組中含有多個(gè)數(shù)據(jù)文件，直接一起創(chuàng)建的時(shí)候，可能會(huì)產(chǎn)生文件id和原來(lái)不一致的情況，這時(shí)可以采用一個(gè)一個(gè)數(shù)據(jù)文件單獨(dú)創(chuàng)建的方式進(jìn)行創(chuàng)建。如果文件id 不一致，用備份的數(shù)據(jù)文件替換新建的數(shù)據(jù)文件時(shí)會(huì)報(bào)錯(cuò)。具體錯(cuò)誤信息如下：文件Id 的查看方法：Use masterSe

7、lect * from sys.master_file;3.2、用備份的數(shù)據(jù)文件替換新的數(shù)據(jù)文件1、首先停止SQL Server服務(wù)2、替換數(shù)據(jù)文件和日志文件3、啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)如果替換時(shí)，報(bào)拒絕訪問(wèn)錯(cuò)誤，需要把數(shù)據(jù)文件的權(quán)限更改為everyone 完全訪問(wèn)3.3、數(shù)據(jù)庫(kù)日志修復(fù)在用備份的數(shù)據(jù)和日志文件替換新建的數(shù)據(jù)文件和日志文件后，啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，數(shù)據(jù)庫(kù)自動(dòng)置為in recovery 狀態(tài)，這時(shí)修復(fù)的時(shí)間可能會(huì)比較長(zhǎng)。 由于日志文件3已經(jīng)發(fā)生損壞，修復(fù)到損壞日志部分的時(shí)候，數(shù)據(jù)庫(kù)會(huì)無(wú)法進(jìn)行修復(fù)。這時(shí)可以采用如下方法處理：sp_configure 'allow updates',

8、1reconfigure with overrideGo3.4、損壞的數(shù)據(jù)庫(kù)日志重建1、停止數(shù)據(jù)庫(kù)服務(wù)2、刪除日志文件3的數(shù)據(jù)文件3、啟動(dòng)數(shù)據(jù)庫(kù)4、查詢數(shù)據(jù)庫(kù)信息select name,filename from sys.sysaltfiles where dbid=5;select * from sys.master_files;5、重建損壞的日志文件如果數(shù)據(jù)庫(kù)沒(méi)有處在質(zhì)疑模式，可能需要手工把數(shù)據(jù)庫(kù)設(shè)置為質(zhì)疑模式：sp_configure 'allow updates', 0reconfigure with overrideGoALTER DATABASE PPmonitor

9、 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這時(shí)數(shù)據(jù)庫(kù)中的表已經(jīng)能夠正常訪問(wèn)，由于某些表仍然存在一致性問(wèn)題，在訪問(wèn)時(shí)會(huì)報(bào)錯(cuò)，可能的報(bào)錯(cuò)信息如下：3

10、.5、數(shù)據(jù)庫(kù)的一致性修復(fù)1、把數(shù)據(jù)庫(kù)置為緊急和單用戶模式reconfigure with overrideGoALTER DATABASE PPmonitor SET EMERGENCYGOALTER DATABASE PPmonitor SET SINGLE_USER;2、修復(fù)數(shù)據(jù)庫(kù)中存在的錯(cuò)誤DBCC CheckDB (PPmonitor, REPAIR_ALLOW_DATA_LOSS)修復(fù)完成后，有部分報(bào)錯(cuò)，具體如下：sg 259, Level 16, State 1, Line 1Ad hoc updates to system catalogs are not allowed.Msg

11、 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:40

12、88798) 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 err

13、ors 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 un

14、it 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 a

15、nd (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

16、 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' (objec

17、t 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 allocati

18、on 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 fixe

19、d 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.

20、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

21、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 databa

22、se 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, al

23、loc 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 = 19

24、38384, 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,

25、contact your system administrator.4、查詢測(cè)試查詢指定條件時(shí)報(bào)錯(cuò)，只能全部按順序掃描，即只能使用select * from table使用select count(*) from table 報(bào)錯(cuò)，具體報(bào)錯(cuò)如下：Attempt to fetch logical page (1:229734) in database 5 failed. It belongs to allocation unit 72057594251182080 not to 72057594275561472.消息 605，級(jí)別 21，狀態(tài) 3，第 2 行嘗試在數(shù)據(jù)庫(kù) 5 中提取邏輯頁(yè) (1

26、:1824) 失敗。該邏輯頁(yè)屬于分配單元 72057594194165760，而非 72057594237091840。5、表無(wú)損修復(fù)把數(shù)據(jù)啟動(dòng)到單用戶模式下ALTER DATABASE PPmonitor SET single_user;GO對(duì)出錯(cuò)的表進(jìn)行修復(fù)DBCC CheckTable(AdjustData_7878_N, REPAIR_REBUILD);AdjustData_7878_N的 DBCC 結(jié)果。修復(fù): 已為數(shù)據(jù)庫(kù) 'PPmonitor' 中的對(duì)象 'dbo.AdjustData_7878_N' 成功地重新生成了 Clustered 索引。消息

27、 8945，級(jí)別 16，狀態(tài) 1，第 1 行表錯(cuò)誤: 將重新生成對(duì)象 ID 368876531，索引 ID 1。 該錯(cuò)誤已修復(fù)。消息 8976，級(jí)別 16，狀態(tài) 1，第 1 行表錯(cuò)誤: 對(duì)象 ID 368876531，索引 ID 1，分區(qū) ID 72057594241220608，分配單元 ID 72057594244825088 (類型為 In-row data)。在掃描過(guò)程中未發(fā)現(xiàn)頁(yè) (3:656)，但該頁(yè)的父級(jí) (7:4810173) 和上一頁(yè) (7:4850791) 都引用了它。請(qǐng)檢查以前的錯(cuò)誤消息。 該錯(cuò)誤已修復(fù)。消息 8980，級(jí)別 16，狀態(tài) 1，第 1 行表錯(cuò)誤: 對(duì)象 ID

28、368876531，索引 ID 1，分區(qū) ID 72057594241220608，分配單元 ID 72057594244825088 (類型為 In-row data)。索引節(jié)點(diǎn)頁(yè) (7:4810173)，槽 617 指向子頁(yè) (3:657) 和上一子頁(yè) (3:656)，但未遇到這些頁(yè)。 該錯(cuò)誤已修復(fù)。消息 8980，級(jí)別 16，狀態(tài) 1，第 1 行表錯(cuò)誤: 對(duì)象 ID 368876531，索引 ID 1，分區(qū) ID 72057594241220608，分配單元 ID 72057594244825088 (類型為 In-row data)。索引節(jié)點(diǎn)頁(yè) (7:4810173)，槽 618 指向

29、子頁(yè) (3:658) 和上一子頁(yè) (3:657)，但未遇到這些頁(yè)。 該錯(cuò)誤已修復(fù)。消息 8980，級(jí)別 16，狀態(tài) 1，第 1 行表錯(cuò)誤: 對(duì)象 ID 368876531，索引 ID 1，分區(qū) ID 72057594241220608，分配單元 ID 72057594244825088 (類型為 In-row data)。索引節(jié)點(diǎn)頁(yè) (7:4810173)，槽 619 指向子頁(yè) (3:659) 和上一子頁(yè) (3:658)，但未遇到這些頁(yè)。 該錯(cuò)誤已修復(fù)。消息 8980，級(jí)別 16，狀態(tài) 1，第 1 行表錯(cuò)誤: 對(duì)象 ID 368876531，索引 ID 1，分區(qū) ID 720575942412

30、20608，分配單元 ID 72057594244825088 (類型為 In-row data)。索引節(jié)點(diǎn)頁(yè) (7:4810173)，槽 620 指向子頁(yè) (3:660) 和上一子頁(yè) (3:659)，但未遇到這些頁(yè)。 該錯(cuò)誤已修復(fù)。對(duì)象 'AdjustData_7878_N' 的 59527 頁(yè)中有 344840 行。CHECKTABLE 在表 'AdjustData_7878_N' (對(duì)象 ID 368876531)中發(fā)現(xiàn) 0 個(gè)分配錯(cuò)誤和 5 個(gè)一致性錯(cuò)誤。CHECKTABLE 在表 'AdjustData_7878_N' (對(duì)象 ID 3

31、68876531)中修復(fù)了 0 個(gè)分配錯(cuò)誤和 5 個(gè)一致性錯(cuò)誤。DBCC 執(zhí)行完畢。如果 DBCC 輸出了錯(cuò)誤信息，請(qǐng)與系統(tǒng)管理員聯(lián)系。查詢表的分配單元可以通過(guò)如下語(yǔ)句：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.partition_idJOIN sys.filegroups AS