管理交易記錄檔的大小
適用於: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,將記錄縮減至大約目標大小。
如需有關壓縮操作的詳細資訊,請檢閱下列連結:
壓縮記錄檔 (但不壓縮資料庫檔案)
監視記錄檔壓縮事件
監視記錄空間
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)。