管理交易記錄檔的大小Manage the size of the transaction log file

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主題涵蓋如何監視 SQL ServerSQL Server 交易記錄大小、壓縮交易記錄、加入或加大交易記錄檔、最佳化 tempdb 交易記錄成長率,以及控制交易記錄檔的成長。This topic covers how to monitor SQL ServerSQL Server transaction log size, shrink the transaction log, add to or enlarge a transaction log file, optimize the tempdb transaction log growth rate, and control the growth of a transaction log file.

監視記錄空間的使用Monitor log space use

使用 sys.dm_db_log_space_usage 來監視記錄空間的使用。Monitor log space use by using sys.dm_db_log_space_usage. 這個 DMV 會傳回目前使用之記錄空間量的相關資訊,並指出交易記錄需要截斷的時機。This DMV returns information about the amount of log space currently used, and indicates when the transaction log needs truncation.

如需目前的記錄檔大小、大小上限及檔案的自動成長選項等詳細資訊,您也可以在 sys.database_files 中使用該記錄檔的 sizemax_sizegrowth 資料行。For information about the current log file size, its maximum size, and the autogrow option for the file, you can also use the size, max_size, and growth columns for that log file in sys.database_files.

重要

請避免讓記錄磁碟多載。Avoid overloading the log disk. 請確定記錄檔儲存體可以承受交易式負載的 IOPS 和低延遲需求。Ensure the log storage can withstand the IOPS and low latency requirements for your transactional load.

壓縮記錄檔大小Shrink log file size

若要減少實體記錄檔的實體大小,則必須壓縮記錄檔。To reduce the physical size of a physical log file, you must shrink the log file. 如果您知道交易記錄檔包含未使用的空間,則這十分有用。This is useful when you know that a transaction log file contains unused space. 只有當資料庫已上線,而且至少有一個虛擬記錄檔 (VLF) 可用時,您才能壓縮記錄檔。You can shrink a log file only while the database is online, and at least one virtual log file (VLF) is free. 在某些情況下,壓縮記錄可能要等到下一個記錄截斷之後才能進行。In some cases, shrinking the log may not be possible until after the next log truncation.

注意

如長時間執行的交易之類的因素,使 VLF 保持作用中一段很長的時間,可能限制記錄檔壓縮,甚至完全阻止記錄檔壓縮。Factors such as a long-running transaction, that keep VLFs active for an extended period, can restrict log shrinkage or even prevent the log from shrinking at all. 如需資訊,請參閱可能會延遲記錄截斷的因素For information, see Factors that can delay log truncation.

壓縮記錄檔會移除一或多個不保留任何邏輯記錄的 VLF (即「非使用中 VLF」 )。Shrinking a log file removes one or more VLFs that hold no part of the logical log (that is, inactive VLFs). 當交易記錄檔壓縮之後,就會從記錄檔的結尾移除非使用中的 VLF,將記錄縮減至大約目標大小。When a transaction log file is shrunk, inactive VLFs are removed from the end of the log file to reduce the log to approximately the target size.

重要

壓縮交易記錄檔之前,請記住可能會延遲記錄截斷的因素Before shrinking the transaction log, keep in mind Factors that can delay log truncation. 如果壓縮記錄檔之後再次需要儲存空間,交易記錄檔將再次成長,並且會因此在記錄檔成長作業期間導入效能額外負荷。If the storage space is required again after a log shrink, the transaction log will grow again and by doing that, introduce performance overhead during log growth operations. 如需詳細資訊,請參閱本主題中的建議For more information, see the Recommendations in this topic.

壓縮記錄檔 (但不壓縮資料庫檔案)Shrink a log file (without shrinking database files)

監視記錄檔壓縮事件Monitor log-file shrink events

監視記錄空間Monitor log space

加入或加大記錄檔Add or enlarge a log file

您可以加大現有的記錄檔 (如果磁碟空間允許的話),或是將記錄檔加入資料庫 (通常是在不同的磁碟上),來取得空間。You can gain space by enlarging the existing log file (if disk space permits) or by adding a log file to the database, typically on a different disk. 除非記錄檔空間不足,且保存記錄檔的磁碟區上磁碟空間也不足,否則一個交易記錄檔便已足夠。One transaction log file is sufficient unless log space is running out, and disk space is also running out on the volume that holds the log file.

  • 若要對資料庫新增一個記錄檔,請使用 ALTER DATABASE 陳述式的 ADD LOG FILE 子句。To add a log file to the database, use the ADD LOG FILE clause of the ALTER DATABASE statement. 新增記錄檔可讓記錄檔增大。Adding a log file allows the log to grow.
  • 若要加大記錄檔,請使用 ALTER DATABASE 陳述式的 MODIFY FILE 子句,並指定 SIZEMAXSIZE 語法。To enlarge the log file, use the MODIFY FILE clause of the ALTER DATABASE statement, specifying the SIZE and MAXSIZE syntax. 如需詳細資訊,請參閱 ALTER DATABASE (Transact-SQL) 檔案及檔案群組選項For more information, see ALTER DATABASE (Transact-SQL) File and Filegroup options.

如需詳細資訊,請參閱本主題中的建議For more information, see the Recommendations in this topic.

最佳化 tempdb 交易記錄的大小Optimize tempdb transaction log size

重新啟動伺服器執行個體時,就會將 tempdb 資料庫的交易記錄大小重新調整為自動成長之前的原始大小。Restarting a server instance resizes the transaction log of the tempdb database to its original, pre-autogrow size. 這樣會降低 tempdb 交易記錄的效能。This can reduce the performance of the tempdb transaction log.

您可以在啟動或重新啟動伺服器執行個體後,增加 tempdb 交易記錄的大小,藉以避免這項負擔。You can avoid this overhead by increasing the size of the tempdb transaction log after starting or restarting the server instance. 如需詳細資訊,請參閱 tempdb DatabaseFor more information, see tempdb Database.

控制交易記錄檔的成長Control transaction log file growth

請使用 ALTER DATABASE (Transact-SQL) 檔案及檔案群組選項陳述式來管理交易記錄檔的成長。Use the ALTER DATABASE (Transact-SQL) File and Filegroup options statement to manage the growth of a transaction log file. 請注意下列事項:Note the following:

  • 若要變更目前的檔案大小 (單位為 KB、MB、GB 和 TB),請使用 SIZE 選項。To change the current file size in KB, MB, GB, and TB units, use the SIZE option.
  • 若要變更成長的增量,請使用 FILEGROWTH 選項。To change the growth increment, use the FILEGROWTH option. 0 的值表示將自動成長設為關閉,而且不允許任何其他空間。A value of 0 indicates that automatic growth is set to off and no additional space is permitted.
  • 若要控制記錄檔大小的最大值 (單位為 KB、MB、GB 和 TB) 或是將成長設定為 UNLIMITED,請使用 MAXSIZE 選項。To control the maximum the size of a log file in KB, MB, GB, and TB units or to set growth to UNLIMITED, use the MAXSIZE option.

如需詳細資訊,請參閱本主題中的建議For more information, see the Recommendations in this topic.

建議Recommendations

以下是關於使用交易記錄檔時的一般建議:Following are some general recommendations when you are working with transaction log files:

  • 交易記錄檔的自動成長 (autogrow) 增量,如 FILEGROWTH 選項所設定,必須要夠大才能保持領先工作負載交易的需求。The automatic growth (autogrow) increment of the transaction log, as set by the FILEGROWTH option, must be large enough to stay ahead of the needs of the workload transactions. 記錄檔的檔案成長量應夠大,才不用經常進行擴充。The file growth increment on a log file should be sufficiently large to avoid frequent expansion. 若要適當設定交易記錄檔的大小,有一項良好的指標就是監視下列期間所佔用的記錄檔數量:A good pointer to properly size a transaction log is monitoring the amount of log occupied during:

    • 執行完整備份所需的時間,因為直到完成為止才會發生記錄檔備份。The time required to execute a full backup, because log backups cannot occur until it finishes.
    • 最大索引維護作業所需的時間。The time required for the largest index maintenance operations.
    • 執行資料庫中最大批次所需的時間。The time required to execute the largest batch in a database.
  • 使用 FILEGROWTH 選項設定資料和記錄檔的 autogrow 時,最好以 [大小] 來設定它,而不是使用 [百分比] ,以便更能控制成長比率,因為百分比是個不斷成長的數量。When setting autogrow for data and log files using the FILEGROWTH option, it might be preferred to set it in size instead of percentage, to allow better control on the growth ratio, as percentage is an ever-growing amount.

    • 請記住,交易記錄檔無法利用立即檔案初始化,因此延伸的記錄成長時間特別重要。Keep in mind that transaction logs cannot leverage Instant File Initialization, so extended log growth times are especially critical.
    • 最佳做法是不要將交易記錄的 FILEGROWTH 選項值設定為超過 1024 MB。As a best practice, do not set the FILEGROWTH option value above 1,024 MB for transaction logs. FILEGROWTH 選項的預設值是:The default values for FILEGROWTH option are:
    VersionVersion 預設值Default values
    SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) 資料 64 MB。Data 64 MB. 記錄檔 64 MB。Log files 64 MB.
    SQL Server 2005 (9.x)SQL Server 2005 (9.x) 開始Starting with SQL Server 2005 (9.x)SQL Server 2005 (9.x) 資料 1 MB。Data 1 MB. 記錄檔 10%。Log files 10%.
    SQL Server 2005 (9.x)SQL Server 2005 (9.x) 之前Prior to SQL Server 2005 (9.x)SQL Server 2005 (9.x) 資料 10%。Data 10%. 記錄檔 10%。Log files 10%.
  • 小型的成長增量可能會產生太多小型 VLF,且可能會降低效能。A small growth increment can generate too many small VLFs and can reduce performance. 若要判斷指定執行個體中所有資料庫的目前交易記錄大小的最佳 VLF 分佈,以及達到所需大小的必要成長增量,請參閱此指令碼To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, see this script.

  • 大型的成長增量可能會產生太少且大型的 VLF,且亦可能會降低效能。A large growth increment can generate too few and large VLFs and can also affect performance. 若要判斷指定執行個體中所有資料庫的目前交易記錄大小的最佳 VLF 分佈,以及達到所需大小的必要成長增量,請參閱此指令碼To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, see this script.

  • 如果無法成長得夠快速以滿足查詢的需求,即使已啟用 autogrow,您還是可能收到訊息,指出交易記錄檔已滿。Even with autogrow enabled, you can receive a message that the transaction log is full, if it cannot grow fast enough to satisfy the needs of your query. 如需變更成長增量的詳細資訊,請參閱 ALTER DATABASE (Transact-SQL) 檔案及檔案群組選項For more information on changing the growth increment, see ALTER DATABASE (Transact-SQL) File and Filegroup options

  • 在資料庫中具有多個記錄檔將無法以任何方式強化效能,因為交易記錄檔不像相同檔案群組中的資料檔案那樣使用比例填滿Having multiple log files in a database does not enhance performance in any way, because the transaction log files do not use proportional fill like data files in a same filegroup.

  • 可以將記錄檔設定為自動壓縮。Log files can be set to shrink automatically. 不過並不建議如此,且 auto_shrink 資料庫屬性預設會設定為 FALSE。However this is not recommended, and the auto_shrink database property is set to FALSE by default. 如果 auto_shrink 設定為 TRUE,只有當超過 25% 的空間未使用時,自動壓縮才會減少檔案的大小。If auto_shrink is set to TRUE, automatic shrinking reduces the size of a file only when more than 25 percent of its space is unused.

另請參閱See also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
針對完整交易記錄 (SQL Server 錯誤 9002) 進行疑難排解 Troubleshoot a Full Transaction Log (SQL Server Error 9002)
SQL Server 中的交易記錄備份交易記錄架構與管理指南 Transaction Log Backups in the SQL Server Transaction Log Architecture and Management Guide
交易記錄備份 (SQL Server) Transaction Log Backups (SQL Server)
ALTER DATABASE (Transact-SQL) 檔案及檔案群組選項ALTER DATABASE (Transact-SQL) File and Filegroup options