管理交易記錄檔的大小

適用於：SQL Server

本文涵蓋如何監視 SQL Server 交易記錄大小、壓縮交易記錄、新增或加大交易記錄檔、最佳化 tempdb 交易記錄增長率，以及控制交易記錄檔的增長。

本文適用於 SQL Server。 雖然非常類似，但如需在 Azure SQL 受控執行個體中管理交易記錄檔大小的資訊，請參閱管理 Azure SQL 受控執行個體中資料庫的檔案空間。 如需有關 Azure SQL 資料庫的詳細資訊，請參閱管理 Azure SQL 資料庫的資料庫空間。

了解資料庫的儲存空間類型

要管理資料庫的檔案空間，務必要了解下列儲存體空間數量。

資料庫數量	定義	註解
已使用的資料空間	用來儲存資料庫資料的空間量。	一般而言，使用的空間會在插入 (刪除) 時增加 (減少)。 在某些情況下，根據作業與任何分割中涉及的資料量和模式而定，使用的空間並不會隨插入或刪除而變更。 例如，從每個資料頁刪除一個資料列，不見得會減少使用的空間。
已配置的資料空間	可用以儲存資料庫資料的格式化檔案空間量。	配置的空間量會自動成長，但絕不會在刪除後減少。 此行為可確保未來能更快地插入，因為不需要重新格式化空間。
已配置但未使用的資料空間	已配置的資料空間量與已使用的資料空間之間的差異。	此數量代表可藉由壓縮資料檔案而回收的可用空間量上限。
資料大小上限	可用來儲存資料庫資料的空間量上限。	配置的資料空間量不得成長超過資料大小上限。

下圖說明資料庫的不同儲存體空間類型之間的關聯性。

查詢單一資料庫來取得檔案空間資訊

使用下列查詢，以傳回已配置的資料庫檔案空間量與已配置但未使用的空間量。 查詢結果以 MB 為單位。

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

監視記錄空間的使用

使用 sys.dm_db_log_space_usage 來監視記錄空間的使用。 這個 DMV 會傳回目前使用之記錄空間量的相關資訊，並指出交易記錄需要截斷的時機。

如需目前的記錄檔大小、大小上限及檔案的自動成長選項等詳細資訊，也可以在 sys.database_files 中使用該記錄檔的 size、max_size 和 growth 資料行。

重要

請避免讓記錄磁碟多載。 請確定記錄檔儲存體可以承受交易式負載的 IOPS 和低延遲需求。

壓縮記錄檔

若要將檔案中的可用空間傳回至作業系統，以減少實體記錄檔的實際大小，請壓縮記錄檔。 當交易記錄檔包含未使用的空間時，壓縮才會產生差異。

如果記錄檔已滿 (可能是因為開啟的交易)，請調查阻止交易記錄截斷的原因。

警告

壓縮作業不應視為一般維修作業。 因定期商務作業成長的資料和記錄檔，不需要壓縮作業。 壓縮命令會在資料庫執行時影響其效能，如果可能，應該在低使用量期間執行。 如果一般應用程式工作負載會讓檔案重新成長到相同的配置大小，即不建議您壓縮資料檔案。

請留意壓縮資料庫檔案可能對效能造成負面的影響，請參閱壓縮後的索引維護。

壓縮交易記錄檔之前，請記住可能會延遲記錄截斷的因素。 如果壓縮記錄檔之後再次需要儲存空間，交易記錄檔將再次成長，並且會因此在記錄檔成長作業期間導入效能額外負荷。 如需詳細資訊，請參閱建議。

只有當資料庫已上線，而且至少有一個虛擬記錄檔 (VLF) 可用時，您才能壓縮記錄檔。 在某些情況下，壓縮記錄可能要等到下一個記錄截斷之後才能進行。

如長時間執行的交易之類的因素，使 VLF 保持作用中一段很長的時間，可能限制記錄檔壓縮，甚至完全阻止記錄檔壓縮。 如需資訊，請參閱可能會延遲記錄截斷的因素。

壓縮記錄檔會移除一或多個不保留任何邏輯記錄的 VLF (即「非使用中 VLF」)。 壓縮交易記錄檔時，會從記錄檔的結尾移除非使用中的 VLF，將記錄縮減至大約目標大小。

如需有關壓縮操作的詳細資訊，請檢閱下列連結：

壓縮記錄檔 (但不壓縮資料庫檔案)

• DBCC SHRINKFILE (Transact-SQL)

• 壓縮檔案

監視記錄檔壓縮事件

• Log File Auto Shrink Event Class。

監視記錄空間

• sys.dm_db_log_space_usage (Transact-SQL)

• sys.database_files (Transact-SQL) (請參閱記錄檔的 size、max_size 和 growth 資料行。)

壓縮後的索引維修

完成資料檔案的壓縮作業後，索引可能會變得分散。 這會降低特定工作負載 (例如使用大型掃描的查詢) 的效能最佳化有效性。 如果壓縮作業完成後發生效能降低的情形，請考慮執行索引維修來重建索引。 請記得，索引重建需要資料庫具有可用空間，並可能導致已配置的空間增加，抵銷壓縮的效果。

如需索引維修的詳細資訊，請參閱將索引維修最佳化以改善查詢效能並降低資源耗用量。

加入或加大記錄檔

您可以加大現有的記錄檔 (如果磁碟空間允許的話)，或是將記錄檔加入資料庫 (通常是在不同的磁碟上)，來取得空間。 除非記錄檔空間不足，且保存記錄檔的磁碟區上磁碟空間也不足，否則一個交易記錄檔便已足夠。

• 若要對資料庫新增一個記錄檔，請使用 ALTER DATABASE 陳述式的 ADD LOG FILE 子句。 新增記錄檔可讓記錄檔增大。
• 若要加大記錄檔，請使用 ALTER DATABASE 陳述式的 MODIFY FILE 子句，並指定 SIZE 和 MAXSIZE 語法。 如需詳細資訊，請參閱 ALTER DATABASE (Transact-SQL) 檔案及檔案群組選項。

如需詳細資訊，請參閱建議。

最佳化 tempdb 交易記錄的大小

重新啟動伺服器執行個體時，就會將 tempdb 資料庫的交易記錄大小重新調整為自動成長之前的原始大小。 這樣會降低 tempdb 交易記錄的效能。

您可以在啟動或重新啟動伺服器執行個體後，增加 tempdb 交易記錄的大小，藉以避免這項負擔。 如需詳細資訊，請參閱 tempdb Database。

控制交易記錄檔的成長

請使用 ALTER DATABASE (Transact-SQL) 檔案及檔案群組選項陳述式來管理交易記錄檔的成長。 請注意：

• 若要變更目前的檔案大小 (單位為 KB、MB、GB 和 TB)，請使用 SIZE 選項。
• 若要變更成長的增量，請使用 FILEGROWTH 選項。 0 的值表示將自動成長設為關閉，而且不允許任何其他空間。
• 若要控制記錄檔大小的最大值 (單位為 KB、MB、GB 和 TB) 或是將成長設定為 UNLIMITED，請使用 MAXSIZE 選項。

如需詳細資訊，請參閱建議。

建議

以下是關於使用交易記錄檔時的一般建議：

• 交易記錄檔的自動成長 (autogrow) 增量，如 FILEGROWTH 選項所設定，必須要夠大才能保持領先工作負載交易的需求。 記錄檔的檔案成長量應夠大，才不用經常進行擴充。 若要適當設定交易記錄檔的大小，有一項良好的指標就是監視下列期間所佔用的記錄檔數量：

  • 執行完整備份所需的時間，因為直到完成為止才會發生記錄檔備份。
  • 最大索引維護作業所需的時間。
  • 執行資料庫中最大批次所需的時間。

• 使用 FILEGROWTH 選項設定資料和記錄檔的 autogrow 時，最好以 [大小] 來設定它，而不是使用 [百分比]，以便更能控制成長比率，因為百分比是個不斷成長的數量。

  • 在 SQL Server 2022 (16.x) 之前的版本中，交易記錄檔無法使用檔案立即初始化，因此延伸的記錄成長時間特別重要。

  • 從 SQL Server 2022 (16.x) (所有版本) 開始並在 Azure SQL Database 中，檔案立即初始化可能有助於最多 64 MB 的交易記錄「成長事件」。 新資料庫的預設自動成長大小增量為 64 MB。 大於 64 MB 的交易記錄檔自動成長事件無法受益於檔案立即初始化。

  • 最佳做法是不要將交易記錄的 FILEGROWTH 選項值設定為超過 1024 MB。 FILEGROWTH 選項的預設值是：

    版本	預設值
    從 SQL Server 2016 (13.x) 開始	資料 64 MB。 記錄檔 64 MB。
    從 SQL Server 2005 (9.x) 開始	資料 1 MB。 記錄檔 10%。
    SQL Server 2005 (9.x) 之前	資料 10%。 記錄檔 10%。

• 小型的自動增長增量可能會產生太多小型 VLF，且可能會降低效能。 若要判斷指定執行個體中所有資料庫的目前交易記錄大小的最佳 VLF 分佈，以及達到所需大小的必要增長增量，請參閱 SQL Tiger 團隊提供的用於分析和修正 VLF 的指令碼。

• 大型自動增長增量可能會導致兩個問題：

  • 大型自動增長增量可能會導致資料庫在配置新空間時暫停，並可能導致查詢逾時。
  • 大型自動增長增量可能會產生太少且大型的 VLF，且亦可能會降低效能。 若要判斷指定執行個體中所有資料庫的目前交易記錄大小的最佳 VLF 分佈，以及達到所需大小的必要增長增量，請參閱 SQL Tiger 團隊提供的用於分析和修正 VLF 的指令碼。

• 如果無法成長得夠快速以滿足查詢的需求，即使已啟用 autogrow，您還是可能收到訊息，指出交易記錄檔已滿。 如需變更成長增量的詳細資訊，請參閱 ALTER DATABASE (Transact-SQL) 檔案及檔案群組選項。

• 在資料庫中具有多個記錄檔將無法以任何方式強化效能，因為交易記錄檔不像相同檔案群組中的資料檔案那樣使用比例填滿。

• 可以將記錄檔設定為自動壓縮。 不過並不建議如此，且 auto_shrink 資料庫屬性預設會設定為 FALSE。 如果 auto_shrink 設定為 TRUE，只有當超過 25% 的空間未使用時，自動壓縮才會減少檔案的大小。

  • 此時，檔案會壓縮成只有 25% 的檔案是未使用空間的大小，或檔案的原始大小，以較大者為準。
  • 如需變更 auto_shrink 屬性設定的資訊，請參閱檢視或變更資料庫的屬性和 ALTER DATABASE SET 選項 (Transact-SQL)。

下一步

• BACKUP (Transact-SQL)
• 寫滿交易記錄疑難排解 (SQL Server 錯誤 9002)
• SQL Server 中的交易記錄備份交易記錄架構與管理指南
• 交易記錄備份 (SQL Server)
• ALTER DATABASE (Transact-SQL) 檔案及檔案群組選項