管理交易記錄檔的大小

適用於：SQL Server

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

本文適用於 SQL Server。 雖然程式類似，但如需 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 會傳回目前使用之記錄空間量的相關資訊，並指出交易記錄需要截斷的時機。

如需目前記錄檔大小、大小上限以及檔案自動成長選項的相關資訊，您也可以在max_size中使用記錄檔的size、max_size和growth欄位。

重要

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

縮小記錄檔

壓縮記錄檔，藉由將可用空間傳回給操作系統，以減少其實體大小。 當交易記錄檔包含未使用的空間時，壓縮才會產生差異。

如果記錄檔已滿，可能是因為正在執行的交易，請調查 阻止交易記錄截斷的原因。

警告

壓縮作業不應被視為定期維護作業。 由於定期週期性商務作業而成長的資料和記錄檔不需要壓縮作業。 壓縮命令在執行時會影響資料庫效能。 它們應該在低使用率期間運行。 如果一般應用程式工作負載會導致檔案再次成長到相同的配置大小，我們不建議您縮小資料檔案。

請注意縮小資料庫檔案對效能的潛在負面影響。 請參閱 收縮後的索引維護。

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

只有在資料庫在線上且至少有一個 虛擬記錄檔 （VLF） 可用時，才能壓縮記錄檔。 在某些情況下，只有在下一個記錄截斷之後，才能壓縮記錄檔。

某些因素 （例如長時間執行的交易） 可能會讓 VLF 長時間保持作用中、限制日誌壓縮，甚至完全可以防止日誌壓縮。 如需詳細資訊，請參閱可能會延遲記錄截斷的因素。

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

如需壓縮作業的詳細資訊，請檢閱下列資源：

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

• DBCC 壓縮檔案 （Transact-SQL）

• 縮小檔案

監視記錄檔壓縮事件

• 記錄檔自動縮小事件類別

監視記錄空間

• Sys.dm_db_log_space_usage （Transact-SQL）

• Sys.database_files （Transact-SQL） （請參閱 size、max_size，和 growth 欄，以獲取一或多個日誌檔案。）

收縮操作後的索引維護

針對數據檔完成壓縮作業之後，索引可能會變得分散。 這種碎片化會降低其針對特定工作負載 （例如使用大型掃描的查詢） 效能最佳化的有效性。 如果壓縮作業完成後發生效能降低的情形，請考慮執行索引維修來重建索引。 請記住，索引重建需要資料庫中的可用空間，因此可能會增加配置的空間，從而抵消壓縮作業的影響。

如需詳細資訊，請參閱 最佳化索引維護以改善查詢效能並減少資源耗用量。

加入或加大記錄檔

您可以藉由放大現有的記錄檔來取得空間（如果磁碟空間允許），或將記錄檔新增至資料庫，通常是在不同的磁碟上。 除非記錄空間用完，而且磁碟區上的磁碟空間也會用盡，否則一個事務歷史記錄檔就已足夠。

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

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

最佳化 tempdb 交易記錄的大小

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

啟動或重新啟動伺服器實例之後， tempdb 您可以增加事務歷史記錄大小來避免此額外負荷。 如需詳細資訊，請參閱 Tempdb 資料庫。

控制交易記錄檔的成長

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

• 使用該 SIZE 選項以 KB、MB、GB 和 TB 為單位變更目前檔案大小。
• 若要變更成長的增量，請使用 FILEGROWTH 選項。 值為 0 表示自動成長設定為關閉，且不允許額外的空間。 使用該 MAXSIZE 選項來控制日誌檔的大小上限，以 KB、MB、GB 及 TB 為單位，或將成長設為 UNLIMITED。

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

建議

以下是使用交易記錄檔時要考慮的一些一般建議：

• 交易日誌的自動成長增量（如選項FILEGROWTH所設定）必須足夠大，以滿足工作量交易的需求。 記錄檔的檔案成長量應夠大，才不用經常進行擴充。 正確調整交易日志大小的一個好提示是監視在以下期間佔用的日志量：

  • 執行完整備份所需的時間，因為在此之前無法進行日誌備份。
  • 最大索引維護作業所需的時間。
  • 在資料庫中執行最大批次所需的時間。

• 當您使用選項 FILEGROWTH 來設定資料及日誌檔的自動成長時，最好將其設定 為大小 ，而不是 百分比 ，以便更好地控制成長比率，因為百分比是不斷增加的量。

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

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

  • 最佳做法是，請勿針對事務歷史記錄設定 FILEGROWTH 高於 1,024 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 小組所提供的 VDF。

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

  • 它可能會導致資料庫在分配新空間時暫停，從而可能導致查詢逾時。
  • 它可能會產生數量太少且大小過大的VLF，也會影響效能。 若要判斷指定實例中所有資料庫目前事務歷史記錄大小的最佳 VLF 散發，以及達到所需大小的所需成長增量，請參閱此 腳本，以分析及修正 SQL Tiger 小組所提供的 VDF。

• 即使已啟用自動成長，如果交易記錄無法成長得足夠快以滿足查詢的需求，您仍會收到一則訊息，指出交易記錄已滿。 如需變更成長增量的詳細資訊，請參閱 ALTER DATABASE （Transact-SQL） 檔案和檔案群組選項。

• 資料庫中有多個記錄檔並不會以任何方式增強效能，因為事務歷史記錄檔不會像相同檔案群組中的數據檔一樣使用 比例填滿 。

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

• 檔案會縮小，直至未使用空間僅占 25% 或回到檔案的原始大小，以較大者為準。
• 如需變更屬性設定AUTO_SHRINK的相關資訊，請參閱 檢視或變更資料庫的屬性 和 ALTER DATABASE SET 選項 （Transact-SQL）。

相關內容

• 備份 （Transact-SQL）
• 針對完整交易記錄進行疑難排解 （SQL Server 錯誤 9002）
• SQL Server 交易記錄架構和管理指南中的交易記錄備份
• 交易記錄備份 （SQL Server）
• ALTER DATABASE （Transact-SQL） 檔案和檔案群組選項