Manage the size of the transaction log file
Applies to: SQL Server
This article covers how to monitor SQL 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.
This article applies to SQL Server. Though very similar, for information on managing the size of transaction log files in Azure SQL Managed Instance, see Manage file space for databases in Azure SQL Managed Instance. For information about Azure SQL Database, see Manage file space for databases in Azure SQL Database.
Understand types of storage space for a database
Understanding the following storage space quantities are important for managing the file space of a database.
|Data space used||The amount of space used to store database data.||Generally, space used increases (decreases) on inserts (deletes). In some cases, the space used does not change on inserts or deletes depending on the amount and pattern of data involved in the operation and any fragmentation. For example, deleting one row from every data page does not necessarily decrease the space used.|
|Data space allocated||The amount of formatted file space made available for storing database data.||The amount of space allocated grows automatically, but never decreases after deletes. This behavior ensures that future inserts are faster since space does not need to be reformatted.|
|Data space allocated but unused||The difference between the amount of data space allocated and data space used.||This quantity represents the maximum amount of free space that can be reclaimed by shrinking database data files.|
|Data max size||The maximum amount of space that can be used for storing database data.||The amount of data space allocated cannot grow beyond the data max size.|
The following diagram illustrates the relationship between the different types of storage space for a database.
Query a single database for file space information
Use the following query to return the amount of database file space allocated and the amount of unused space allocated. Units of the query result are in 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;
Monitor log space use
Monitor log space use by using sys.dm_db_log_space_usage. This DMV returns information about the amount of log space currently used, and indicates when the transaction log needs truncation.
For information about the current log file size, its maximum size, and the autogrow option for the file, you can also use the
growth columns for that log file in sys.database_files.
Avoid overloading the log disk. Ensure the log storage can withstand the IOPS and low latency requirements for your transactional load.
Shrink log file
To reduce the physical size of a physical log file by returning free space in the file to the operating system, shrink the log file. A shrink only makes a difference when a transaction log file contains unused space.
If the log file is full, likely because of open transactions, investigate what is preventing transaction log truncation.
Shrink operations should not be considered a regular maintenance operation. Data and log files that grow due to regular, recurring business operations do not require shrink operations. Shrink commands impact database performance while running, and if possible should be run during periods of low usage. It is not recommended to shrink data files if regular application workload will cause the files to grow to the same allocated size again.
Be aware of the potential negative performance impact of shrinking database files, see Index maintenance after shrink.
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.
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.
Factors, such as a long-running transaction, can 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.
Shrinking a log file removes one or more VLFs that hold no part of the logical log (that is, inactive VLFs). When you shrink a transaction log file, inactive VLFs are removed from the end of the log file to reduce the log to approximately the target size.
For more information on shrink operations, review the following links:
Shrink a log file (without shrinking database files)
Monitor log-file shrink events
Monitor log space
sys.database_files (Transact-SQL) (See the
growthcolumns for the log file or files.)
Index maintenance after shrink
After a shrink operation is completed against data files, indexes may become fragmented. This reduces their performance optimization effectiveness for certain workloads, such as queries using large scans. If performance degradation occurs after the shrink operation is complete, consider index maintenance to rebuild indexes. Keep in mind that index rebuilds require free space in the database, and hence may cause the allocated space to increase, counteracting the effect of shrink.
For more information about index maintenance, see Optimize index maintenance to improve query performance and reduce resource consumption.
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.
- To add a log file to the database, use the
ADD LOG FILEclause of the
ALTER DATABASEstatement. Adding a log file allows the log to grow.
- To enlarge the log file, use the
MODIFY FILEclause of the
ALTER DATABASEstatement, specifying the
MAXSIZEsyntax. For more information, see ALTER DATABASE (Transact-SQL) File and Filegroup options.
For more information, see the Recommendations.
Optimize tempdb transaction log size
Restarting a server instance resizes the transaction log of the
tempdb database to its original, pre-autogrow size. This can reduce the performance of the
tempdb transaction log.
You can avoid this overhead by increasing the size of the
tempdb transaction log after starting or restarting the server instance. For more information, see tempdb Database.
Control transaction log file growth
Use the ALTER DATABASE (Transact-SQL) File and Filegroup options statement to manage the growth of a transaction log file. Note the following:
- To change the current file size in KB, MB, GB, and TB units, use the
- To change the growth increment, use the
FILEGROWTHoption. A value of 0 indicates that automatic growth is set to off and no additional space is permitted.
- To control the maximum size of a log file in KB, MB, GB, and TB units or to set growth to UNLIMITED, use the
For more information, see the Recommendations.
Following are some general recommendations when you are working with transaction log files:
The automatic growth (autogrow) increment of the transaction log, as set by the
FILEGROWTHoption, 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.
When setting autogrow for data and log files using the
FILEGROWTHoption, 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.
In versions prior to SQL Server 2022 (16.x), transaction logs cannot use Instant File Initialization, so extended log growth times are especially critical.
Starting with SQL Server 2022 (16.x) (all editions) and in Azure SQL Database, instant file initialization can benefit transaction log growth events up to 64 MB. The default auto growth size increment for new databases is 64 MB. Transaction log file autogrowth events larger than 64 MB cannot benefit from instant file initialization.
As a best practice, do not set the
FILEGROWTHoption value above 1,024 MB for transaction logs. The default values for
Version Default values Starting with SQL Server 2016 (13.x) Data 64 MB. Log files 64 MB. Starting with SQL Server 2005 (9.x) Data 1 MB. Log files 10%. Prior to SQL Server 2005 (9.x) Data 10%. Log files 10%.
A small autogrowth increment can generate too many small VLFs and can reduce performance. 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 for analyzing and fixing VLFs, provided by the SQL Tiger Team.
A large autogrowth increment can cause two problems:
- A large autogrowth increment can cause the database to pause while the new space is allocated, potentially causing query timeouts.
- A large autogrowth increment can generate too few and large VLFs and can also affect performance. 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 for analyzing and fixing VLFs, provided by the SQL Tiger Team.
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. 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. However this is not recommended, and the auto_shrink database property is set to FALSE by default. 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.
- The file is shrunk either to the size at which only 25 percent of the file is unused space or to the original size of the file, whichever is larger.
- For information about changing the setting of the auto_shrink property, see View or Change the Properties of a Database and ALTER DATABASE SET Options (Transact-SQL).