管理交易記錄檔的大小

適用於：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 會傳回目前使用之記錄空間量的相關資訊，並指出交易記錄需要截斷的時機。

如需目前記錄檔大小、其大小上限，以及檔案的自動成長選項的相關信息，您也可以使用 sizesys.database_files 中該記錄檔的、 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) 檔案及檔案群組選項陳述式來管理交易記錄檔的成長。 請注意：

使用 '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%。

• 小型成長增量可能會產生太多小型 VDF ，並可降低效能。 若要判斷指定實例中所有資料庫目前事務歷史記錄大小的最佳 VLF 散發，以及達到所需大小的所需成長增量，請參閱此 腳本，以分析及修正 SQL Tiger 小組所提供的 VDF。

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

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

• 即使已啟用自動成長，您仍會收到訊息，指出如果事務歷史記錄無法成長到足以滿足查詢需求，則表示事務歷史記錄已滿。 如需變更成長增量的詳細資訊，請參閱 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) 檔案及檔案群組選項