Msg 9002 The transaction log for database ” is full

今天有個朋友說他的資料庫報錯，錯誤資訊如下：

 

Msg 9002, Level 17, State 2, Line 4
The transaction log for database '' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

 

這個錯誤是很常見的，記錄檔滿了，但是導致日誌滿的原因會有很多，怎麼查呢？其實這個錯誤已經給了我們很大的提示，查詢sys.databases中的log_reuse_wait_desc列．查詢sys.databases:

 

SELECT log_reuse_wait_descFROMsys.databasesWHERE[name]='database';

得到的值為：LOG_BACKUP,說明需要作業記錄備份解決這個問題。所以對資料庫進行一個記錄備份：

 

BACKUPLOG DatabaseAtoDISK='D:\MSSQL\databaseAckup.trn';

備份完成後問題解決，之後再查log_reuse_wait_desc得到值為：NOTHING(當前有一個或多個可重複使用的虛擬記錄檔。)

 

下面我列出log_reuse_wait_desc的所有值以及每個值對應的說明（可能延遲日誌截斷的因素）

 

log_reuse_wait值	log_reuse_wait_desc值	說明
0	NOTHING	當前有一個或多個可重複使用的虛擬記錄檔。
1	CHECKPOINT	自上次日誌截斷之後，尚未出現檢查點，或者日誌頭部尚未跨一個虛擬記錄檔移動（所有復原模式）。 這是日誌截斷延遲的常見原因。有關詳細資料，請參閱檢查點和日誌的活動部分。
2	LOG_BACKUP	需要記錄備份，以將日誌的頭部前移（僅適用於完整復原模式或大量記錄復原模式）。 注意 記錄備份不會妨礙截斷。 完成記錄備份後，日誌的頭部將前移，一些日誌空間可能變為可重複使用。
3	ACTIVE_BACKUP_OR_RESTORE	資料備份或還原進行中（所有復原模式）。 資料備份與活動事務的運行方式相同。資料備份在運行時，將阻止截斷。有關詳細資料，請參閱本主題後面的“資料備份操作與還原作業”部分。
4	ACTIVE_TRANSACTION	事務處於活動狀態（所有復原模式）。 ·         一個長時間啟動並執行事務可能存在於記錄備份的開頭。在這種情況下，可能需要進行另一個記錄備份才能釋放空間。有關詳細資料，請參閱本主題後面的“長時間啟動並執行活動事務”部分。 ·         事務被延遲（僅適用於 SQL Server 2005 Enterprise Edition及更高版本）。“延遲交易”是有效活動事務，因為某些資源不可用，其復原受阻。有關導致事務延遲的原因以及如何使它們擺脫延遲狀態的資訊，請參閱延遲交易。
5	DATABASE_MIRRORING	資料庫鏡像暫停，或者在高效能模式下，鏡像資料庫明顯滯後於主體資料庫（僅限於完整復原模式）。 有關詳細資料，請參閱本主題後面的“資料庫鏡像與交易記錄”部分。
6	REPLICATION	在事務複製過程中，與發布相關的事務仍未傳遞到散發資料庫（僅限於完整復原模式）。 有關詳細資料，請參閱本主題後面的“事務複製與交易記錄”部分。
7	DATABASE_SNAPSHOT_CREATION	正在建立資料庫快照集（所有復原模式）。 這是日誌截斷延遲的常見原因，通常也是主要原因。
8	LOG_SCAN	進行中日誌掃描（所有復原模式）。 這是日誌截斷延遲的常見原因，通常也是主要原因。
9	OTHER_TRANSIENT	當前未使用此值。

 

另外一個常見的引起日誌滿的因素是Longrunningtransaction，我們在查詢sys.databases後可以看到ACTIVE_TRANSACTION，然後根據DBCC
OPENTRAN找出沒有Commit的SPID進行處理。

 

下面是運行DBCC OPENTRAN的結果集：

 

Transaction information for database 'master'.

Oldest active transaction:

SPID (server process ID) : 52

UID (user ID) : -1

Name          : user_transaction

LSN           : (518:1576:1)

Start time    : Jun 1 2004 3:30:07:197PM

SID           : 0x010500000000000515000000a065cf7e784b9b5fe77c87709e611500

DBCC execution completed. If DBCC printed error messages, contact your system administrator.