SQLServer日志損壞造成整個數(shù)據(jù)庫損壞的修復(fù)

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

2、拷貝。我們把備份的數(shù)據(jù)庫文件拷貝到異機進行附加，總是報錯，提示無法讀取日志文件。具體報錯如下：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通過上面的報錯可知，數(shù)據(jù)庫的日志文件發(fā)生了損壞，這時已經(jīng)不能通過簡單的附加方式進行恢復(fù)，也無法通過無日志附加的方式進行附加，因為這時數(shù)據(jù)庫處在一個不一致的狀態(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 可以通過sys.master_files進行查看三、處理過程 由于這時數(shù)據(jù)庫已經(jīng)無法正常打開（已經(jīng)沒有對應(yīng)的數(shù)據(jù)庫，或數(shù)據(jù)庫狀態(tài)錯誤，查看不了任何屬性信息），所以我們必須重新創(chuàng)建同名的數(shù)據(jù)庫，然后用備份的數(shù)據(jù)文件覆蓋新創(chuàng)建的數(shù)據(jù)文件。處理的主要過程如下：3.1、新建同名的數(shù)據(jù)庫。1、新建數(shù)據(jù)庫時，要求數(shù)據(jù)文件目錄、路徑、數(shù)據(jù)文件大小要和原來保持一致。如果是恢復(fù)到其他機器，要保證SQL Server實例的版本和原來保持一致，小版本也要保持一致，如果

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

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

8、1reconfigure with overrideGo3.4、損壞的數(shù)據(jù)庫日志重建1、停止數(shù)據(jù)庫服務(wù)2、刪除日志文件3的數(shù)據(jù)文件3、啟動數(shù)據(jù)庫4、查詢數(shù)據(jù)庫信息select name,filename from sys.sysaltfiles where dbid=5;select * from sys.master_files;5、重建損壞的日志文件如果數(shù)據(jù)庫沒有處在質(zhì)疑模式，可能需要手工把數(shù)據(jù)庫設(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ù)據(jù)庫中的表已經(jīng)能夠正常訪問，由于某些表仍然存在一致性問題，在訪問時會報錯，可能的報錯信息如下：3

10、.5、數(shù)據(jù)庫的一致性修復(fù)1、把數(shù)據(jù)庫置為緊急和單用戶模式reconfigure with overrideGoALTER DATABASE PPmonitor SET EMERGENCYGOALTER DATABASE PPmonitor SET SINGLE_USER;2、修復(fù)數(shù)據(jù)庫中存在的錯誤DBCC CheckDB (PPmonitor, REPAIR_ALLOW_DATA_LOSS)修復(fù)完成后，有部分報錯，具體如下：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、查詢測試查詢指定條件時報錯，只能全部按順序掃描，即只能使用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，狀態(tài) 3，第 2 行嘗試在數(shù)據(jù)庫 5 中提取邏輯頁 (1

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

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

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

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

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

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