SQL Server的“錯誤：9004”

標籤：style   blog   http   color   使用   os   io   strong   

客戶的一台現場採集電腦崩潰，無法啟動。用PE引導後，將MDF和LDF資料複製出來，往正常的資料庫附加時，報如下錯誤：

處理資料庫 ‘databasename‘ 的日誌時出錯。如果可能，請從備份還原。如果沒有可用備份，可能需要重建日誌。無法開啟新資料庫 ‘databasename‘。CREATE DATABASE 中止。 (Microsoft SQL Server，錯誤: 9004)

尋找資料以後，凡是帶有“update sysdatabases set status=-32768 where dbid=DB_ID(‘test‘)”這個語句的，一律無效，會報“不允許對系統目錄進行即席更新。”，sql server2005以後不允許更新系統目錄了。

以下方法經我測試有效：

 

解決步驟：
1．使用預設建立一個供恢複使用的資料庫(如database_forrestore)。
2．停止資料庫執行個體服務。
3．將剛才產生的資料庫的記錄檔database_forrestore.ldf刪除，用要恢複的資料庫mdf檔案覆蓋剛才產生的資料庫資料檔案database_forrestore_data.mdf。
4．啟動資料庫伺服器。此時會看到資料庫database_forrestore不可訪問。
5. ALTER DATABASE database_forrestore SET EMERGENCY
6. ALTER DATABASE database_forrestore SET SINGLE_USER【這一步是為了執行下面的dbcc】
7. ALTER DATABASE database_forrestore REBUILD LOG ON (NAME=dbname_log, FILENAME=‘d:\data\database_forrestore_log.ldf‘)
    執行後，會有警告資訊“警告: 資料庫 ‘database_forrestore‘ 的日誌已重建。已失去事務的一致性。RESTORE 鏈已斷開，伺服器不再有以前的記錄檔的上下文，因此您需要瞭解它們的內容。應運行 DBCC CHECKDB 驗證物理一致性。資料庫已置於 dbo-only 模式。在準備使資料庫可用時，需要重設資料庫選項，並刪除所有多餘的記錄檔。”
8. DBCC CHECKDB(<dbname>, REPAIR_ALLOW_DATA_LOSS) 【這一步時，我這裡報655錯誤，版本不相容，讓我升級資料庫，我的sqlserver伺服器是2012,用sp_dbcmptlevel查看database_forrestore 其相容層級是110，也是2012，應該不存在相容問題，但是這一步就是執行不了，可以跳過】
9. ALTER DATABASE database_forrestore  SET MULTI_USER【恢複多使用者模式】
10. ALTER DATABASE database_forrestore  SET ONLINE 【聯機，可以在資料庫上的右鍵菜單中執行】

11.重新整理，至此，資料應該回來了

 

 

這裡還有個英文版可以參考

 

ERROR : 9004 An error occurred while processing the log for database.
If possible, restore from backup.
If a backup is not available, it might be necessary to rebuild the log.

 

If you receive above error it means you are in great trouble. This error occurs when database is attempted to attach and it does not get attached. I have solved this error using following methods. Hope this will help anybody who is facing the same error.

 

Microsoft suggest there are two solution to this problem.

 

1) Restore from a backup.

 

• Create Empty Database with same name and physical files (.ldf and .mdf).
• Shut down SQL Server.
• Replace the files which you want to attach with this new empty database files.
• Start SQL Server.
• Database will be in suspect mode which means so far everything going as it should be.
• Next is to put database in emergency mode. ALTER DATABASE <DatabaseName> SET EMERGENCY will change the database status to emergency.
• A database which is in emergency mode can be repaired with allowing some data loss. DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS will do the task.
• In this process some of the data may be lost but database will in working condition with most of the data retrieved from log.

 

2) Rebuild the log.

 

• Create database using CREATE DATABASE FOR ATTACH_REBUILD_LOG.
• This may fix the problem right away if database which is attempted to be attach is shut down properly. If database is corrupt it will be not restored correctly.
• Next DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS should be ran to bring back the database operational. This may loose some data but it may bring back database in working condition.
• In this option as log is rebuilt SQL Server does not use log file to retrieve any data. In this process none of the log operation like transaction rollback will work.

 

I prefer to use the first method if it works. First method has worked sevral time without any issue. Syntax of the CREATE database can be found on Book Online for further guidance.

 

Reference : Pinal Dave (http://blog.SQLAuthority.com)