如何避免 SQL Server 資料庫的交易記錄檔超出預期大小

本文將告訴您，當交易記錄檔擴充到超出預期限制時，必須依照哪些步驟來處理。交易記錄檔擴充，可能會造成 Microsoft SQL Server 資料庫無法使用。本文也會提供一些選項，可以用來避免交易記錄檔超出預期大小。

在 SQL Server 2000 和 SQL Server 2005 中，每個資料庫至少都包含一個資料檔以及一個交易記錄檔。SQL Server 實際上會將資料儲存在資料檔中。交易記錄檔則是儲存您在 SQL Server 資料庫中執行的所有修改的詳細資料，以及執行每項修改時的詳細交易資料。因為交易完整性被視為 SQL Server 的基本特性，因此無法關閉 SQL Server 中，詳細交易資料的記錄功能。

交易記錄檔會依照邏輯分成較小的區段，稱之為虛擬記錄檔。在 SQL Server 2000 中，您可以設定交易記錄檔依照需要來擴充。交易記錄檔的擴充範圍可由使用者決定，或者也可以設定為使用所有的可用磁碟空間。SQL Server 對交易記錄檔大小所做的任何修改 (例如，截斷交易記錄檔或擴充交易記錄檔)，在執行時都是以虛擬記錄檔為單位。

如果對應於 SQL Server 資料庫的交易記錄檔已滿，而且您已經設定了讓交易記錄檔自動擴充的選項，則交易記錄檔將會以虛擬記錄檔為單位來進行擴充。有時候，交易記錄檔可能會變得非常大，而導致磁碟空間不足。當交易記錄檔擴充到用完所有的可用磁碟空間，而無法再繼續擴充時，您就無法再對資料庫執行任何資料修改的作業。此外，如果因為沒有空間可供交易記錄檔進行擴充時，SQL Server 可能會將資料庫標示為可疑 (Suspect)。

如需有關哪些情況可能造成交易記錄檔超出預期大小的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：

縮減交易記錄檔的大小

如果要復原超出預期限制的交易記錄檔，您必須縮減交易記錄檔的大小。如果要執行這項操作，必須截斷交易記錄檔中的非現用交易，並且壓縮交易記錄檔。

注意 交易記錄檔對於維持資料庫的交易完整性是非常重要的。因此，您千萬不能刪除交易記錄檔，即使是已為資料庫和交易記錄檔製作備份後，也不能刪除。

截斷交易記錄檔中的非現用交易

當交易記錄檔超出預期限制時，必須立即備份交易記錄檔。在建立交易記錄檔的備份時，SQL Server 會自動截斷交易記錄檔中的非現用部份。交易記錄檔中的非現用部份包含已經完成的交易，因此，在復原過程中，SQL Server 不會再用到交易記錄檔。SQL Server 會重新使用交易記錄檔中已截斷的非現用空間，而不是讓交易記錄檔繼續擴充並且使用更多的空間。

如需有關製作交易記錄檔備份時所必須考慮的問題，以及還原交易記錄檔備份時所必須考慮的問題等詳細資訊，請參閱《SQL Server 線上叢書》中的下列主題：

• 交易記錄檔備份
• 交易記錄檔備份與還原

您也可以使用截斷方式，來刪除交易記錄檔中的非現用交易。如需有關截斷交易記錄檔的詳細資訊，請參閱《SQL Server 線上叢書》中的＜截斷交易記錄檔＞主題。

重要 在您以手動方式截斷交易記錄檔後，必須先建立完整的資料庫備份，才能建立交易記錄檔備份。

如需有關截斷交易記錄檔時可能發生的問題的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：

壓縮交易記錄檔

備份作業或截斷方式都不會縮減記錄檔的大小。如果要縮減交易記錄檔的大小，必須壓縮交易記錄檔。如果要將交易記錄檔壓縮成所需的大小，並且移除未使用的頁面，必須使用 DBCC SHRINKFILE 作業。但是，DBCC SHRINKFILE Transact-SQL 陳述式只能壓縮記錄檔內的非現用部份。

注意 DBCC SHRINKFILE Transact-SQL 陳述式本身並不能截斷記錄檔，也不能壓縮記錄檔內的已使用空間。

如需有關壓縮交易記錄檔的詳細資訊，請參閱《SQL Server 線上叢書》中的下列主題：

• 壓縮交易記錄檔
• DBCC SHRINKFILE

如需有關如何在 SQL Server 2000 中壓縮交易記錄檔的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件： 如需有關壓縮交易記錄檔時可能發生的問題的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：

避免交易記錄檔超出預期大小

如果要避免交易記錄檔超出預期大小，請考慮使用下列其中一種方式：

• 將交易記錄檔的大小設定為較大的值，以避免交易記錄檔自動擴充。
• 在您充分評估最佳記憶體大小後，設定交易記錄檔自動擴充時使用記憶體單位，而不是使用百分比。
  
  如需有關設定自動擴充 (Autogrow) 選項時應考慮的問題的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
  315512? (http://support.microsoft.com/kb/315512/ ) INF：Autogrow 及 Autoshrink 設定的考量
• 變更復原模式。發生嚴重損毀或資料損毀時，必須復原資料庫，如此才能維持資料庫的資料一致性與交易完整性。您可以根據資料庫中資料的重要性，使用下列其中一種復原模式，來決定資料的備份方式以及可能造成的資料損失：
  • 簡易復原模式
  • 完整復原模式
  • 大量登入復原模式
  如果使用簡易復原模式，可以將資料庫復原為最近一次的資料庫備份；如果使用完整復原模式或大量登入復原模式，則可以利用交易記錄檔的備份來還原資料庫，將資料庫復原為發生故障前的那個時點。
  
  根據預設，在 SQL Server 2000 和 SQL Server 2005 中，SQL Server 資料庫的復原模式被設定為「完整」復原模式。使用完整復原模式時，會利用定期製作的交易記錄檔備份，來避免交易記錄檔的大小超出資料庫大小的一定比例。但是，如果沒有定期製作交易記錄檔備份，交易記錄檔就會不斷擴充使用磁碟空間，如此一來，您可能就無法再對 SQL Server 資料庫執行任何資料修改的作業。
  
  如果您不想在損毀復原作業中使用交易記錄檔，可以將復原模式從完整變更為簡易。
• 定期備份交易記錄檔，將交易記錄檔中的非現用交易予以刪除。
• 設計成少量交易。
• 確定未交付的交易不會無止境地執行下去。
• 排定每天執行 [更新統計資料] 選項的時間。
• 如果要重組索引，以提升生產環境的工作效能，請使用 DBCC INDEXDEFRAG Transact-SQL 陳述式，而不要使用 DBCC DBREINDEX Transact-SQL 陳述式。如果執行 DBCC DBREINDEX 陳述式，當 SQL Server 資料庫採用「完整」復原模式時，交易記錄檔可能會大量擴充。此外，DBCC INDEXDEFRAG 陳述式不會長時間鎖定，這點也與 DBCC DBREINDEX 陳述式不同。
  
  如需有關在 SQL Server 2000 中重組索引的詳細資訊，請參閱下列 Microsoft 網站：
  http://www.microsoft.com/taiwan/technet/prodtechnol/sql/2000/maintain/ss2kidbp.aspx (http://www.microsoft.com/taiwan/technet/prodtechnol/sql/2000/maintain/ss2kidbp.aspx)
  如果執行 DBCC DBREINDEX 陳述式是資料庫維護計劃中的一項必要工作，您就必須將這項工作分成多項工作進行。此外，在執行這些工作的期間，也必須經常備份交易記錄檔。

如需有關在復原過程中如何解決額外磁碟空間需求問題的詳細資訊，請參閱《SQL Server 線上叢書》中的＜磁碟空間不足＞主題。如需有關交易記錄檔架構的詳細資訊，請參閱《SQL Server 線上叢書》中的下列主題：

• 交易記錄檔架構
• 交易記錄檔的邏輯架構
• 交易記錄檔的實體架構

如需有關 SQL Server 2000 中各種復原模式的詳細資訊，請參閱《SQL Server 線上叢書》中的下列主題：

• 選擇復原模式
• 簡易復原
• 完整復原
• 大量登入復原
• 切換復原模式