搜尋本站文章

2010-02-15

交易記錄檔(transaction log)意外損毀,僅剩下資料檔案可用為例。錯誤訊息:訊息 1813,層級 16,狀態 2 ... 檔案啟動錯誤。實體檔案名稱 "*.ldf" 可能不正確。

這裡所指的意外中斷服務情境,例如:
1.系統意外、不正常中斷服務。
2.磁碟損毀。
3.交易記錄檔案(*.ldf)損毀。
等...


發生上述的情境時,若仍是採取一般方式來附加資料庫時,但因為資料庫未正常關閉,導致交易記錄檔案毀損時,將遇到以下的錯誤訊息:

檔案啟動錯誤。實體檔案名稱 "C:\myAdmin\DB\DBLog_log.ldf" 可能不正確。
無法重建記錄,因為關閉資料庫時仍有開啟的交易/使用者、資料庫未發生檢查點,或資料庫是唯讀的。如果手動刪除交易記錄檔,或因硬體或環境失敗而遺失交易記錄檔,就可能發生這種錯誤。
訊息 1813,層級 16,狀態 2,行 1
無法開啟新資料庫 'DBLog'。CREATE DATABASE 已經中止。








情境:
模擬系統損毀,僅剩下資料檔案,沒有交易記錄檔案(*.ldf)。



可能的搶救方式:

適用版本:
SQL Server 2005 與 2008


--步驟01. 請先依據此資料庫的檔案結構,重新建立相同結構、名稱的資料庫。
--步驟02. 正常離線此資料庫,例如執行:
USE master
GO
ALTER DATABASE [資料庫名稱]
SET OFFLINE;
GO



/*=========================================================*/
--步驟03. 將先前的資料檔案覆蓋掉此新建立的資料庫檔案。
--步驟04. 若嘗試執行重新上線此資料庫,例如:
USE master
GO
ALTER DATABASE [資料庫名稱]
SET ONLINE;
GO

/* 遇到的錯誤訊息:
訊息 5173,層級 16,狀態 1,行 1
一或多個檔案與資料庫的主要檔案不符。如果您嘗試附加資料庫,請以正確檔案重試作業。如果這是現有的資料庫,則檔案可能已損毀,應該從備份還原。
記錄檔 'C:\myAdmin\DB\DBLog_log.ldf' 與主要檔案不符。可能是因為來自於不同的資料庫,或是記錄檔先前已經重建過。

訊息 945,層級 14,狀態 2,行 1
檔案無法存取、記憶體或磁碟空間不足,因此無法開啟資料庫 'DBLog'。詳細資訊請參閱 SQL Server 錯誤記錄檔。

訊息 5069,層級 16,狀態 1,行 1
ALTER DATABASE 陳述式失敗。
*/



--步驟05. 查詢資料庫的狀態為:RECOVERY_PENDING 與 MULTI_USER
/*
RECOVERY PENDING 資料庫狀態
SQL Server 在復原期間發生資源相關的錯誤。
資料庫並未損毀,但是檔案有可能遺失或系統資源限制有可能造成它無法啟動。
資料庫是無法使用的。需要使用者執行其他動作以解決錯誤並讓復原處理得以完成。

MULTI_USER
當指定 MULTI_USER 時,可讓所有具有適當權限的使用者都連接到允許的資料庫。
*/
SELECT name N'資料庫', state_desc N'資料庫狀態', user_access_desc N'使用者存取設定'
FROM sys.databases
GO



/*=========================================================*/
--步驟06. 先設定此資料庫的狀態為:SINGLE_USER
/*
SINGLE_USER 資料庫狀態
指定每次只能有一位使用者存取資料庫。
當指定 SINGLE_USER 時,每次只有一個使用者可以連接到資料庫。所有其他的使用者連接都會中斷。

如果指定了 SINGLE_USER,且沒有其他使用者連接到資料庫,就會封鎖 ALTER DATABASE 陳述式,直到所有使用者都中斷連接指定的資料庫為止。
若要覆寫這個行為,請參閱 WITH  子句。

資料庫會保留在 SINGLE_USER 模式中,即使是將選項記錄設為關閉的使用者也是如此。此時其他使用者可以連接到這個資料庫,但只能有一位。

將資料庫設為 SINGLE_USER 之前,請先確定 AUTO_UPDATE_STATISTICS_ASYNC 選項是否設為 OFF。
當設為 ON 時,更新統計資料的背景執行緒會取得資料庫連接,而您就無法以單一使用者模式存取資料庫。
若要檢視這個選項的狀態,請查詢 sys.databases 目錄檢視中的 is_auto_update_stats_async_on 資料行。

使用終止選項 WITH ROLLBACK IMMEDIATE。
所有未完成的交易都會回復,而且資料庫的任何其他連接都會立即中斷。
*/
ALTER DATABASE [資料庫名稱]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

/* 遇到的錯誤訊息:
訊息 5173,層級 16,狀態 1,行 1
一或多個檔案與資料庫的主要檔案不符。如果您嘗試附加資料庫,請以正確檔案重試作業。如果這是現有的資料庫,則檔案可能已損毀,應該從備份還原。
記錄檔 'C:\myAdmin\DB\DBLog_log.ldf' 與主要檔案不符。可能是因為來自於不同的資料庫,或是記錄檔先前已經重建過。
訊息 945,層級 14,狀態 2,行 1
檔案無法存取、記憶體或磁碟空間不足,因此無法開啟資料庫 'DBLog'。詳細資訊請參閱 SQL Server 錯誤記錄檔。
訊息 5069,層級 16,狀態 1,行 1
ALTER DATABASE 陳述式失敗。
*/
-- 查詢資料庫屬性已經變更為:SINGLE_USER
SELECT name N'資料庫', state_desc N'資料庫狀態', user_access_desc N'使用者存取設定'
FROM sys.databases
GO





/*=========================================================*/
--步驟07. 設定資料庫狀態為:EMERGENCY
/*
EMERGENCY  資料庫狀態
使用者已變更資料庫並將狀態設為 EMERGENCY。
資料庫是在單一使用者模式下,而且可以進行修復或還原。

資料庫是標示為 READ_ONLY、記錄已停用並限定只有系統管理員 (sysadmin) 固定伺服器角色的成員才可存取。
EMERGENCY 主要是做為疑難排解的用途。例如,標示為有疑問的資料庫可以設為 EMERGENCY 狀態。

在這個情況下,系統管理員可以進行資料庫的唯讀存取。
只有系統管理員 (sysadmin) 固定伺服器角色的成員,可以將 資料庫設定為 EMERGENCY 狀態。
*/
ALTER DATABASE  [資料庫名稱]
SET EMERGENCY;
GO

-- 查詢資料庫狀態已經變更為:EMERGENCY 與 SINGLE_USER
SELECT name N'資料庫', state_desc N'資料庫狀態', user_access_desc N'使用者存取設定'
FROM sys.databases
GO





/*=========================================================*/
--步驟08. 執行 DBCC CHECKDB 搭配使用 REPAIR_ALLOW_DATA_LOSS 參數。
/*
REPAIR_ALLOW_DATA_LOSS 參數
指定的資料庫必須為單一使用者模式,才能使用。
嘗試修復所有報告的錯誤。這些修復可能會造成某些資料的遺失。

以資料庫緊急模式解決錯誤
如果已經利用 ALTER DATABASE 陳述式將資料庫設為緊急模式,則 DBCC CHECKDB 可以在資料庫上執行某些特殊的修復作業 (如果已指定 REPAIR_ALLOW_DATA_LOSS 選項)。

這些修復可讓一般無法修復的資料庫以實體一致的狀態重新上線。
只有在資料庫無法從備份還原時,才應該使用上述修復來當做最後手段。
*/
DBCC CHECKDB ( [資料庫名稱] , REPAIR_ALLOW_DATA_LOSS) ;
GO

/* 顯示的訊息:
訊息 5173,層級 16,狀態 1,行 1
一或多個檔案與資料庫的主要檔案不符。如果您嘗試附加資料庫,請以正確檔案重試作業。如果這是現有的資料庫,則檔案可能已損毀,應該從備份還原。
記錄檔 'C:\myAdmin\DB\DBLog_log.ldf' 與主要檔案不符。可能是因為來自於不同的資料庫,或是記錄檔先前已經重建過。

警告: 資料庫 'DBLog' 的記錄已經重建。已遺失交易一致性。RESTORE 鏈已中斷,伺服器已經沒有先前記錄檔的內容,因此您必須知道這些內容。
您應該執行 DBCC CHECKDB 來驗證實體一致性。資料庫已進入僅限 DBO 模式。您準備好讓資料庫能供使用後,必須重設資料庫選項,並刪除任何額外的記錄檔。

DBCC 對 'DBLog' 的結果。
...
*/



--步驟09. 正確依序完成步驟08 後,交易記錄檔案已經重新建立完成。

/*=========================================================*/
--步驟10. 查詢資料庫狀態已經變更為:ONLINE 與 SINGLE_USER。
SELECT name N'資料庫', state_desc N'資料庫狀態', user_access_desc N'使用者存取設定'
FROM sys.databases
GO





--步驟11. 再度執行 DBCC CHECKDB 檢查指定資料庫中所有物件的邏輯完整性和實體完整性
use master
GO
DBCC CHECKDB( [資料庫名稱])
GO

/*=========================================================*/
--步驟12. 設定此資料庫的狀態為:MULTI_USER,回復正常上線
/*
當指定 MULTI_USER 時,可讓所有具有適當權限的使用者都連接到允許的資料庫。
*/
ALTER DATABASE [資料庫名稱]
SET MULTI_USER;
GO

--步驟13. 查詢資料庫狀態已經變更為:ONLINE 與 MULTI_USER。
SELECT name N'資料庫', state_desc N'資料庫狀態', user_access_desc N'使用者存取設定'
FROM sys.databases
where name='DBLog'
GO








參考資料:
自動重建交易記錄檔案(*.ldf),以正常卸離情況為例
http://sharedderrick.blogspot.com/2010/02/ldf.html

資料庫狀態
http://msdn.microsoft.com/zh-tw/library/ms190442.aspx

DBCC CHECKDB (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms176064.aspx

INF:有修復選項的 DBCC CHECKDB/CHECKTABLE 要求資料庫必須為「單一使用者模式」
http://support.microsoft.com/kb/264154/zh-tw

In Recovery... CHECKDB From Every Angle: EMERGENCY mode repair - the very, very last resort
http://www.sqlskills.com/blogs/paul/post/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort.aspx

When should you rebuild the transaction log?
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/15/632398.aspx

Which part of REPAIR_ALLOW_DATA_LOSS isnt clear?
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/16/633645.aspx

Ta da! Emergency mode repair
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx

attaching DB without .ldf file ??
http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/189be01f-23de-48b0-96cc-8f1292c13c54

2 則留言:

  1. 這篇太重要了...幾乎救了我一命

    回覆刪除
  2. 也提醒您,記得定期備份資料庫

    回覆刪除