同事處理異常斷電資料庫狀態變為SUSPECT過程

     墨西哥工廠機房失火，異常斷電後開啟報表伺服器,發現一個資料庫OTS狀態變為SUSPECT，不能查詢，不能檢視屬性，不能備份。

Windows 2003 sp2 +SQL Server 2005 sp2 

 

1.嘗試ONLINE資料庫,失敗。

  Database 'OTS' cannot be opened - it has been marked SUSPECT by recover Explanation 

  檢視對應的資料檔案和日誌檔案,存在.

2.執行checkdb ‘OTS’,提示Database 'OTS' cannot be opened.

3.關閉SQL SERVER,拷出MDF,LDF檔案.

       *遇到資料庫有問題時，最好不要用SQL SERVER的備份,因為全備份會截斷事務日誌,可能造成資料庫無法恢復.也不要做detach和delete動作,這樣可能MDF檔再也附加不上去,資料庫徹底沒用了.事實上,此時也無法進行這些動作。

4.檢查磁碟空間是否足夠,MEM是否正常.

  如果磁碟不再有可用空間,無法完成restore過程,資料庫也會被置為suspect狀態.

5.開啟SQL SERVER，用sa賬號登入。

USE   master  

  GO  

  sp_configure   'allow   updates',   1  

  GO  

  RECONFIGURE   WITH   OVERRIDE  

  GO

 

  執行sp_resetstatus  'OTS',關閉ots資料庫的置疑標誌。完成後資訊如下：

 

  Database   'OTS'   status   reset!  

  WARNING:   You   must   reboot   SQL   Server   prior   to   accessing   this   database!

 

  sp_configure   'allow   updates',   0  

  GO  

  RECONFIGURE   WITH   OVERRIDE  

  GO 

6.重啟SQL SERVER,故障依舊。

7.嘗試將OTS資料庫改名後新建一同名資料庫,失敗，不能rename.

8.刪除掉OTS資料庫,嘗試用備份出來的MDF和LDF檔新建一個同名資料庫。

  *不到萬不得已,千萬不要做刪除的動作。事實證明,OTS資料庫刪除後,資料檔案和日誌檔案再也不能附加上去,故障不能重現。

9.將MDF和LDF檔案拷到其他磁碟 ,再附加,報同樣錯誤.

10.使用sp_attach_single_db只附加MDF檔,仍然報錯.

 

  SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x5b206e03; actual: 0x2acd7ef5). It occurred during a read of page (1:79842) in database ID 7 at offset 0x00000026fc4000 in file 'D:\Data\OTS.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

11.關閉資料庫,對磁碟進行掃描.同時修復壞磁碟錯誤.這個過程持續了相當長時間.完成後,再次開啟SQL SERVER,故障依舊.

12.用CREATE DATABASE DBName ON ( FILENAME = N'DBFile' ) FOR ATTACH_REBUILD_LOG附加資料庫時出現提示：The log cannot be rebuilt because the database was not cleanly shut down.

13.此時已經沒有OTS庫可以拿來做恢復了,只好在網上尋找恢復工具.找到一個Recovery for SQL Server,可以從MDF和LDF中將大部分結構和資料恢復出來,但是有部分資料物件有問題，特別是儲存過程有一些亂碼.

14.新建同名的資料庫OTS(資料庫檔名也跟以前一樣)，停止資料庫服務，用COPY出來的.mdf檔案覆蓋新資料庫的同名檔案，啟動資料庫服務。

執行alter database OTS set emergency，將資料庫設定為emergency mode

執行下面的命令恢復資料庫：

use master

exec sp_dboption 'OTS', N'single', N'true' --將目標資料庫置為單使用者狀態

dbcc checkdb('OTS')

資料庫CHECK發現大量GAM，SGAM分配錯誤，和類似以下資訊：

Msg 8935, Level 16, State 1, Line 1

Table error: Object ID 862626116, index ID 1, partition ID 72057594055360512, alloc unit ID 72057594063814656 (type In-row data). The previous link (1:66955) on page (1:1183) does not match the previous page (1:200244) that the parent (1:69276), slot 23 expects for this page.

Msg 8935, Level 16, State 1, Line 1

Table error: Object ID 862626116, index ID 1, partition ID 72057594055360512, alloc unit ID 72057594063814656 (type In-row data). The previous link (1:41230) on page (1:1757) does not match the previous page (1:91286) that the parent (1:57623), slot 6

 

透過系統表檢視 Object ID 862626116的TABLE，發現PK欄位有重複值。

因為此表資料是從其他DB抄過來的，所以資料丟失也沒有關係，所以執行下面的語句。

 

dbcc checkdb('OTS',REPAIR_ALLOW_DATA_LOSS）

提示上面提到的TABLE PK欄位有重複值，而GAM，SGAM分配錯誤已經被repair

 

dbcc checkdb('OTS',REPAIR_REBUILD)

仍然提示上面提到的TABLE PK欄位有重複值。我們暫時不管它，將目標資料庫置為多使用者狀態

 

exec sp_dboption 'OTS', N'single', N'false'

 

此時資料庫已經可用，再來看那個有問題的TABLE，constraint已經沒有了，只剩下PK和index,我們將PK取消掉，查詢出有重複值的行，刪除掉，再建上PK。

 

再次執行checkdb 'OTS',已經沒有錯誤。

 

最後做一次資料庫的完整備份。

 

 

 

總結:

 

1.出現問題時不要驚慌，驚慌的後果往往是不理性的思考甚至不思考就做出一些不可逆的動作。

2.沒有把握時,不要輕易做分離資料庫、刪除資料庫、重啟資料庫伺服器動作。

3.在資料庫可以開啟的時候，使用CheckDB，等待它執行完成並報告所有錯誤，再根據這些錯誤來制定修復策略，雖然這個過程會比較長。

4.CheckDB並不能解決一切問題，所有資料只能靠完整的備份來恢復。所以，備份重於一切。如果實在沒有辦法，也可以藉助一些工具，透過讀取MDF，LDF檔，生成指令碼的方法來恢復結構和資料。

5.不管修復動作能不能成功，都要注意查詢產生問題的原因，防止再次出現類似問題。