對這文章發表回應
發表限制: 非會員 可以發表
管理交易記錄
這個主題討論交易記錄空間的例行管理，以防止交易記錄太滿。
記錄截斷 (在簡單復原模式下自動執行) 是避免記錄填滿不可或缺的動作。截斷處理序會將未保存任何邏輯記錄部分的虛擬記錄檔標示成非使用中，藉以縮減邏輯記錄檔的大小。不過，在某些情況下，實體壓縮或擴展實體記錄檔會很有用。
| |
|---|
記錄檔的自動成長量小也會降低效能。記錄檔的檔案成長量應夠大，才不用經常進行擴充。通常適當的預設成長量為 10%。如需有關對記錄檔變更檔案成長屬性的詳細資訊，請參閱＜ ALTER DATABASE (Transact-SQL)＞。 |
- 交易記錄截斷
包含記錄截斷的相關資訊。
- 管理交易記錄檔的大小
包含監視交易記錄大小、壓縮交易記錄、加入或移除交易記錄檔及最佳化 tempdb 交易記錄成長速率的相關資訊。
-
可能會延遲記錄截斷的因素 描述可能會延遲記錄截斷的因素。
原文出處：Microsoft Technet 管理交易記錄
交易記錄截斷
若永遠不將記錄從交易記錄中刪除，交易記錄就會持續成長，直到填滿實體記錄檔可用的所有磁碟空間為止。記錄截斷會自動釋出邏輯記錄中的空間，以供交易記錄重複使用。
除了因為某個原因而造成延遲以外，記錄截斷會在下列情況下自動發生：
在簡單復原模式下，發生在檢查點之後。
在完整復原模式或大量記錄復原模式下，上一次備份以來發生檢查點時的記錄備份之後。如需詳細資訊，請參閱本主題稍後的「在完整或大量記錄復原模式下的記錄截斷」。
雖然記錄截斷是自動發生的，但是可能會因為各種因素而延遲。如需有關延遲記錄截斷之因素的詳細資訊，請參閱＜ 可能會延遲記錄截斷的因素＞。
 重要事項 |
|---|
如果在記錄截斷中發生長時間的延遲，交易記錄可能會填滿。如需有關如何處理完整交易記錄的詳細資訊，請參閱＜ 寫滿交易記錄疑難排解 (錯誤 9002)＞。 |
如需有關記錄截斷的架構資訊，請參閱本主題稍後的「記錄截斷的運作方式」。
在完整復原模式或大量記錄復原模式下，記錄的非使用中部分要等到其所有記錄檔記錄都已經在記錄備份中擷取之後，才會被截斷。這是維護 「記錄鏈結」 (具有未中斷記錄序號 (LSN) 的一連串記錄檔記錄) 所需的項目。假設下列條件均成立，當您備份交易記錄後，就會截斷記錄：
自從上次備份記錄之後發生檢查點。在完整復原模式或大量記錄復原模式下，截斷記錄一定需要檢查點，但是只有檢查點還是不夠。在檢查點之後，記錄會維持完整，至少到下一次交易記錄備份為止。
如需詳細資訊，請參閱＜ 檢查點與記錄檔的使用中部份＞。
沒有其他因素妨礙記錄截斷。
一般情況下，使用定期備份時，記錄空間將定期釋放以供日後使用。但是，其他因素 (例如，長時間執行的交易) 可能會暫時導致記錄無法截斷。如需詳細資訊，請參閱＜ 可能會延遲記錄截斷的因素＞。
BACKUP LOG 陳述式未指定 WITH COPY_ONLY。
備份交易記錄
| 附註 |
|---|
截斷作業不會讓實體記錄檔變小。縮減記錄檔的實體大小需要壓縮檔案。如需有關壓縮實體記錄檔大小的詳細資訊，請參閱＜ 壓縮交易記錄檔＞。 |
交易記錄是循環使用的檔案。建立資料庫時，邏輯記錄檔會從實體記錄檔開頭處開始。新的記錄會加在邏輯記錄檔的尾端，並朝向實體記錄檔的尾端擴充。資料庫中的交易記錄會對應到一或多個實體檔。SQL Server Database Engine 會在內部將每個實體記錄檔分成數個虛擬記錄檔。記錄截斷會釋出邏輯記錄檔中的空間，其方式是從邏輯記錄檔的開頭刪除非使用中的虛擬記錄檔。如需有關交易記錄架構的深入資訊，請參閱＜
交易記錄邏輯架構＞和＜ 交易記錄檔實體架構＞。
虛擬記錄檔是可重複使用的空間單位。只有僅包含非使用中記錄檔記錄的虛擬記錄檔才可以被截斷。交易記錄的使用中部分 ( 「使用中記錄」) 無法截斷，因為需要使用中記錄來還原資料庫。最近的檢查點會定義使用中記錄。記錄最多可以截斷至該記錄點。
| 附註 |
|---|
如需虛擬記錄檔功能的詳細資訊，請參閱＜ 交易記錄檔實體架構＞。 |
執行檢查點時，交易記錄的非使用中部分會標示成可重複使用。之後，記錄截斷就可以釋出非使用中的部分。截斷會釋出非使用中的虛擬記錄檔以便重複使用。最後，當新記錄寫入釋出的虛擬記錄檔後，該虛擬記錄檔就會再次成為使用中。
記錄於檢查點的某項資訊是必須存在之第一筆記錄檔記錄的記錄序號 (LSN)，才能成功回復整個資料庫。此 LSN 就稱為 「最小復原 LSN」 ( MinLSN)。記錄使用中部分的開頭就是含有 MinLSN 的虛擬記錄。截斷交易記錄後，只有此虛擬記錄檔前面的記錄檔記錄才能釋出以便重複使用。
下圖將顯示截斷前後的交易記錄。第一張圖是顯示從未進行截斷的交易記錄。目前，邏輯記錄正使用四個虛擬記錄檔。虛擬記錄是從第一個虛擬記錄檔的前面開始，並於虛擬記錄 4 結束。MinLSN 記錄位於虛擬記錄 3 中。虛擬記錄 1 和虛擬記錄 2 僅包含非使用中的記錄檔記錄。這些記錄都可以截斷。虛擬記錄 5 仍未使用而且不屬於目前邏輯記錄的一部分。
第二張圖是顯示記錄截斷之後的內容。虛擬記錄 1 和虛擬記錄 2 已經釋出以便重複使用。邏輯記錄檔現在會從虛擬記錄檔 3 的開頭開始。虛擬記錄檔 5 仍未使用，而且不屬於目前邏輯記錄檔的一部分。
管理交易記錄檔的大小 Manage the Size of the Transaction Log File
本主題涵蓋如何監視 SQL Server SQL Server 交易記錄大小、壓縮交易記錄、加入或加大交易記錄檔、最佳化 tempdb 交易記錄成長率，以及控制交易記錄檔的成長。 This topic covers how to monitor SQL Server SQL Server transaction log size, shrink the transaction log, add to or enlarge a transaction log file, optimize the tempdb transaction log growth rate, and control the growth of a transaction log file.
監視記錄空間的使用 Monitor log space use
使用 DBCC SQLPERF (LOGSPACE) 來監視記錄空間的使用。
Monitor log space use by using DBCC SQLPERF (LOGSPACE). 這個命令會傳回目前使用之記錄空間量的相關資訊，並指出交易記錄需要截斷的時機。 This command returns information about the amount of log space currently used, and indicates when the transaction log needs truncation. 如需詳細資訊，請參閱 DBCC SQLPERF Transact-SQL。 For more information, see DBCC SQLPERF Transact-SQL.
如需目前的記錄檔大小、大小上限及檔案的自動成長選項等詳細資訊，您也可以在 sys.database_files 中使用該記錄檔的 size、max_size 和 growth 資料行。 For information about the current log file size, its maximum size, and the autogrow option for the file, you can also use the size, max_size, and growth columns for that log file in sys.database_files. 如需詳細資訊，請參閱 sys.database_files (Transact-SQL)。 For more information, see sys.database_files (Transact-SQL).
重要！ Important! 請避免讓記錄磁碟多載！ Avoid overloading the log disk!
壓縮記錄檔大小 Shrink log file size
若要減少實體記錄檔的實體大小，則必須壓縮記錄檔。 To reduce the physical size of a physical log file, you must shrink the log file. 如果您知道交易記錄檔包含未使用的空間，則這十分有用。 This is useful when you know that a transaction log file contains unused space. 只有當資料庫已上線，而且至少有一個虛擬記錄檔可用時，您才能壓縮記錄檔。 You can shrink a log file only while the database is online, and at least one virtual log file is free. 在某些情況下，壓縮記錄可能要等到下一個記錄截斷之後才能進行。
In some cases, shrinking the log may not be possible until after the next log truncation.
注意
像長時間執行的交易之類的因素，使虛擬記錄檔保持作用中一段很長的時間，可能限制記錄檔壓縮，甚至完全阻止記錄檔壓縮。 Factors, such as a long-running transaction, that keep virtual log files active for an extended period can restrict log shrinkage or even prevent the log from shrinking at all. 如需延遲記錄截斷可能因素的相關資訊，請參閱 交易記錄 (SQL Server)。 For information about factors that can delay log truncation, see The Transaction Log (SQL Server).
壓縮記錄檔會移除一或多個不保留任何邏輯記錄的虛擬記錄檔 (即「非使用中虛擬記錄檔」)。
Shrinking a log file removes one or more virtual log files that hold no part of the logical log (that is, inactive virtual log files). 當交易記錄檔壓縮之後，就會從記錄檔的結尾移除非使用中的虛擬記錄檔，將記錄縮減至大約目標大小。 When a transaction log file is shrunk, inactive virtual log files are removed from the end of the log file to reduce the log to approximately the target size.
壓縮記錄檔 (但不壓縮資料庫檔案) Shrink a log file (without shrinking database files)
DBCC SHRINKFILE (Transact-SQL) DBCC SHRINKFILE (Transact-SQL)
監視記錄檔壓縮事件 Monitor log-file shrink events
Log File Auto Shrink Event Class。 Log File Auto Shrink Event Class.
監視記錄空間 Monitor log space
sys.database_files (Transact-SQL) (請參閱一或多個記錄檔的 size、max_size 和 growth 資料行。) sys.database_files (Transact-SQL) (See the size, max_size, and growth columns for the log file or files.)
注意
您可以將記錄檔設定為自動壓縮。 You can set log files to shrink automatically. 不過，我們建議您不要進行自動壓縮，而且 autoshrink 資料庫屬性預設為 FALSE。 However, we recommend against automatic shrinking, and the autoshrink database property is set to FALSE by default. 如果 autoshrink 設定為 TRUE，只有當超過 25% 的空間未使用時，自動壓縮才會減少檔案的大小。 If autoshrink is set to TRUE, automatic shrinking reduces the size of a file only when more than 25 percent of its space is unused. 此時，檔案會壓縮成只有 25% 的檔案是未使用空間的大小，或檔案的原始大小，以較大者為準。 The file is shrunk either to the size at which only 25 percent of the file is unused space or to the original size of the file, whichever is larger. 如需變更 autoshrink 屬性設定的相關資訊，請參閱
檢視或變更資料庫的屬性—使用 [選項] 頁面的 Auto Shrink 屬性—或 ALTER DATABASE SET 選項 (Transact-SQL)—使用 AUTO_SHRINK 選項。 For information about changing the setting of the autoshrink property, see View or Change the Properties of a Database—use the Auto Shrink property on the Options page—or
ALTER DATABASE SET Options (Transact-SQL)—use the AUTO_SHRINK option.
加入或加大記錄檔 Add or enlarge a log file
您可以加大現有的記錄檔 (如果磁碟空間允許的話)，或是將記錄檔加入資料庫 (通常是在不同的磁碟上)，來取得空間。 You can gain space by enlarging the existing log file (if disk space permits) or by adding a log file to the database, typically on a different disk.
若要對資料庫新增一個記錄檔，請使用 ALTER DATABASE 陳述式的 ADD LOG FILE 子句。 To add a log file to the database, use the ADD LOG FILE clause of the ALTER DATABASE statement. 新增記錄檔可讓記錄檔增大。 Adding a log file allows the log to grow.
若要加大記錄檔，可以使用 ALTER DATABASE 陳述式的 MODIFY FILE 子句，並指定 SIZE 與 MAXSIZE 語法。 To enlarge the log file, use the MODIFY FILE clause of the ALTER DATABASE statement, specifying the SIZE and MAXSIZE syntax. 如需詳細資訊，請參閱 ALTER DATABASE (Transact-SQL)。 For more information, see ALTER DATABASE (Transact-SQL).
最佳化 tempdb 交易記錄的大小 Optimize tempdb transaction log size
重新啟動伺服器執行個體時，就會將 tempdb 資料庫的交易記錄大小重新調整為自動成長之前的原始大小。
Restarting a server instance resizes the transaction log of the tempdb database to its original, pre-autogrow size. 這樣會降低 tempdb 交易記錄的效能。 This can reduce the performance of the tempdb transaction log. 您可以在啟動或重新啟動伺服器執行個體後，增加 tempdb 交易記錄的大小，藉以避免這項負擔。 You can avoid this overhead by increasing the size of the tempdb transaction log after starting or restarting the server instance. 如需詳細資訊，請參閱 tempdb Database。 For more information, see tempdb Database.
控制交易記錄檔的成長 Control transaction log file growth
請使用 ALTER DATABASE (Transact-SQL) 陳述式來管理交易記錄檔的成長。 Use the ALTER DATABASE (Transact-SQL) statement to manage the growth of a transaction log file. 請注意下列事項： Note the following:
- 若要變更目前的檔案大小 (單位為 KB、MB、GB 和 TB)，請使用 SIZE 選項。 To change the current file size in KB, MB, GB, and TB units, use the SIZE option.
- 若要變更成長的增量，請使用 FILEGROWTH 選項。
To change the growth increment, use the FILEGROWTH option. 0 的值表示將自動成長設為關閉，而且不允許任何其他空間。 A value of 0 indicates that automatic growth is set to off and no additional space is permitted. 記錄檔的少量自動成長增量可能會降低效能。 A small autogrowth increment on a log file can reduce performance. 記錄檔的檔案成長量應夠大，才不用經常進行擴充。 The file growth increment on a log file should be sufficiently large to avoid frequent expansion. 通常適當的預設成長量為 10%。 The default growth increment of 10 percent is generally suitable.
- 若要變更成長的增量，請使用 FILEGROWTH 選項。
如需對記錄檔變更檔案成長屬性的相關資訊，請參閱 ALTER DATABASE (Transact-SQL)。
For information on changing the file-growth property on a log file, see ALTER DATABASE (Transact-SQL).
- 若要控制記錄檔大小的最大值 (單位為 KB、MB、GB 和 TB) 或是將成長設定為 UNLIMITED，請使用 MAXSIZE 選項。 To control the maximum the size of a log file in KB, MB, GB, and TB units or to set growth to UNLIMITED, use the MAXSIZE option.
另請參閱 See also
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
寫滿交易記錄疑難排解 (SQL Server 錯誤 9002) Troubleshoot a Full Transaction Log (SQL Server Error 9002)
可能會延遲記錄截斷的因素
記錄截斷會釋出記錄檔中的空間，以供交易記錄重複使用。由於記錄的使用中部分無法被截斷或是以壓縮的方式加以移除，因此當記錄檔記錄長時間保持使用中狀態時，截斷可能就會延遲。
| 附註 |
|---|
如需有關記錄截斷如何運作的詳細資訊，請參閱＜ 交易記錄截斷＞。 |
記錄檔記錄可能會在各種情況下維持使用中狀態，本主題將會加以說明。若要探索是否有任何原因導致記錄截斷無法進行，請使用 sys.databases 目錄檢視的 log_reuse_wait 和 log_reuse_wait_desc 資料行。
| 附註 |
|---|
這些因素中有一部分 (例如，長時間執行的交易或已暫停的資料庫鏡像工作階段) 會導致交易記錄填滿的結果。如需有關如何回應完整交易記錄的詳細資訊，請參閱＜ |
下表簡短描述 sys.database 目錄檢視的 log_reuse_wait 和 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 | 交易在使用中 (所有復原模式)。
|
5 | DATABASE_MIRRORING | 資料庫鏡像已暫停，或者在高效能模式下，鏡像資料庫已大幅落後主體資料庫 (僅限完整復原模式)。 如需詳細資訊，請參閱本主題稍後的「資料庫鏡像與交易記錄」。 |
6 | REPLICATION | 進行交易式複寫期間，與發行集相關的交易仍然未傳遞至散發資料庫 (僅限完整復原模式)。 如需詳細資訊，請參閱本主題稍後的「交易式複寫與交易記錄」。 |
7 | DATABASE_SNAPSHOT_CREATION | 正在建立資料庫快照集 (所有復原模式)。 這是延遲記錄截斷的一般原因 (通常也是暫時的原因)。 |
8 | LOG_SCAN | 正在進行記錄掃描 (所有復原模式)。 這是延遲記錄截斷的一般原因 (通常也是暫時的原因)。 |
9 | OTHER_TRANSIENT | 這個值目前尚未使用。 |
任何備份或還原作業期間都不會發生記錄截斷的狀況。在 SQL Server 2005 及更新的版本中，記錄備份可以在資料備份期間進行。不過，您不能在這種記錄備份期間截斷記錄，因為所有的交易記錄都必須保留給資料備份作業使用。如果資料備份阻礙截斷記錄，取消該備份可能有助於化解眼前的問題。
如需有關記錄截斷的詳細資訊，請參閱＜ 交易記錄截斷＞。
使用中交易會要求包含交易開頭的記錄檔記錄保持使用中狀態。例如，如果交易的開始和結束是由使用者控制，長時間執行之交易的常見原因就是使用者開始進行交易，然後在交易等候使用者回應時離開。在這種情況下，雖然等候交易本身產生的記錄很少，但是它會阻擋記錄截斷動作，而導致記錄檔變大。
| 附註 |
|---|
如需有關如何避免長時間執行之交易的詳細資訊，請參閱＜ 撰寫有效率的交易＞。 |
合併式複寫和快照式複寫不會影響交易記錄大小，但是交易式複寫會影響。如果資料庫包含一個或多個交易式發行集，則除非已將所有與發行集相關的交易傳遞至散發資料庫，否則不會截斷記錄。如果交易記錄變得很大，並且「記錄讀取器代理程式」依排程執行，請考慮縮短兩次執行間的間隔或設定其以連續模式執行。如果已將其設定為在連續模式下執行 (預設值)，則請確定它正在執行。如需有關如何檢查「記錄讀取器代理程式」狀態的詳細資訊，請參閱＜
如何：檢視與發行集相關聯之代理程式的資訊並執行工作 (複寫監視器) ＞。
此外，如果您已在發行集資料庫或散發資料庫中設定選項 'sync with backup'，則除非已備份所有交易，否則交易記錄不會被截斷。如果交易記錄變得很大，並且您已設定此選項，則請考慮縮短兩次交易記錄備份之間的間隔。如需有關如何備份和還原涉及交易式複寫之資料庫的詳細資訊，請參閱＜ 備份與還原快照式和交易式複寫的策略＞。
管理複寫
監視複寫