管理交易記錄檔的大小

適用于:SQL Server (所有支援的版本)

本主題涵蓋如何監視SQL Server交易記錄大小、壓縮交易記錄檔、新增或放大交易記錄檔、優化tempdb交易記錄成長率,以及控制交易記錄檔的成長。

監視記錄空間的使用

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

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

重要

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

壓縮記錄檔大小

若要減少實體記錄檔的實體大小,則必須壓縮記錄檔。 如果您知道交易記錄檔包含未使用的空間,則這十分有用。 只有當資料庫已上線,而且至少有一個虛擬記錄檔 (VLF) 可用時,您才能壓縮記錄檔。 在某些情況下,壓縮記錄可能要等到下一個記錄截斷之後才能進行。

注意

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

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

重要

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

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

監視記錄檔壓縮事件

監視記錄空間

加入或加大記錄檔

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

  • 若要對資料庫新增一個記錄檔,請使用 ALTER DATABASE 陳述式的 ADD LOG FILE 子句。 新增記錄檔可讓記錄檔增大。
  • 若要加大記錄檔,請使用 ALTER DATABASE 陳述式的 MODIFY FILE 子句,並指定 SIZEMAXSIZE 語法。 如需詳細資訊,請參閱 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 分佈,以及達到所需大小的必要成長增量,請參閱此指令碼

  • 大型的成長增量可能會產生太少且大型的 VLF,且亦可能會降低效能。 若要判斷指定執行個體中所有資料庫的目前交易記錄大小的最佳 VLF 分佈,以及達到所需大小的必要成長增量,請參閱此指令碼

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

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

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

另請參閱

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