29 June 2021

39613 views

29 0

Managing SQL Server transaction log size

The SQL Server transaction log must be managed to keep a database running and performing well. In this article, Greg Larsen explains how to manage the transaction log size.

The transaction log is a file that contains all the data modifications that are made. The size of a transaction log is impacted by the frequency of updates to a database, the recovery model, the frequency of checkpoints, and how often transaction log backups are run. Ideally, a transaction log should never need to grow because it is sized based on the database processing requirements. In reality, transaction logs grow, mainly for new databases, but sometimes even the log files of mature databases can grow. This article covers transaction log growth, how the log grows over time, and managing SQL Server transaction log size.

Sizing the transaction log

Ideally, you should size your transaction log so it will never need to grow, but in the real world, it is hard to predict how big a transaction log needs to be. Most of the time, the transaction log for a new database will be too small or too big. When it is too small, it will need to be expanded, and when it is too large, it wastes valuable disk space. Therefore, it is best to create a transaction log as big as you think it needs to be, set it up to autogrow, and then monitor it over time to see how much space it uses and how often it grows.

Transaction log growth settings

There are two settings associated with the growth of the transaction log: file growth and max file size. The file growth setting identifies how much additional space will be added to the transaction log each time it grows. Whereas, the max file size sets the upper limit on how big the transaction log can get.

File growth settings

The transaction log size can be fixed or can be set up to autogrow. A transaction log that has a fixed size will not grow when it runs out of space. When a transaction fills up the transaction log the transaction will fail with a 9002 error msg. Alternatively, you can set up the transaction log to autogrow. When the transaction log is set up to autogrow, it will grow automatically when the transaction log becomes full. There are two different settings for how the transaction log will autogrow: by percentage or by megabytes.

When the transaction log is set up to grow based on a percentage, the amount it grows is calculated based on the percentage value of the current size of the transaction log. For example, if the transaction log is currently 100 GB in size and specified to grow by 15%, then 15 GB will be added to the transaction log when it runs out of space and grows. When the transaction log is set to grow based on percentages, the amount of space added increases with each autogrowth.

Growing in percentages does not scale well as the transaction log grows larger. It doesn’t scale well because more space is allocated with each successive autogrow operation as the transaction log gets bigger. This might be fine for small transaction logs, but when the transaction logs become quite large, autogrowth based on a percentage might allocate way more space than the transaction log will ever need. In the worst-case situation, it might take up all of the disk space available, leaving no free space on the disk drive, which is usually not a good thing.

When the log file is set to autogrow based on a fixed size, the transaction log will grow the same amount each time. If a transaction log for a database is set to autogrow by a fixed amount of 10 GB, it will grow 10 GB each time it needs to grow. Using a fixed growth space setting is much more manageable than using a percentage. Therefore best practice is to grow the transaction log in a fixed amount to avoid adding more transaction log space than needed when using the percentage growth setting.

Maximum file size

The maximum file size setting for the transaction log identifies the maximum size a transaction log can be. There are two different options for maximum file size: limited and unlimited. By using limited, you can identify a maximum size the transaction log will grow. Using the limited setting allows you to make sure the transaction log doesn’t grow out of control. Once the transaction log grows to the limit, it will not grow any more, until the limit is increased. The unlimited setting allows the transaction log to grow as big as it needs to, or until it takes up all the disk space. In reality, using the unlimited setting is also a limited setting, but with a very large size, because SQL Server at this time only supports transaction logs that are 2 TB or less. If you set the maximum file size to unlimited, the maximum size for the transaction log will be set to the limiting amount of 2 TB.

Viewing or changing the file growth setting of an existing database

You can use SSMS to view the file growth settings for an existing database by reviewing the “Autogrowth/Maxsize” settings. These settings are shown in the ”Files” section of the database properties pane, by right-clicking on a database and then selecting the “Database Properties” option from context menu displayed. Figure 1 shows my SampleDB using the “In Percent” options for file growth with a “Maximum File Size” that is limited to 10 MB.

Image showing SSMS autogrowth settings

Figure 1: Database Properties pane for SampleDB

If you need to change the autogrowth setting of a database, you can do it using SSMS by adjusting the settings shown in Figure 1, and then clicking on the OK button. Alternatively, you can change the file growth settings by using TSQL.

To change the autogrowth setting for the transaction log using TSQL, you issue an ALTER DATBASE MODIFY FILE command. The FILEGROWTH, and MAXSIZE parameters are used to change the autogrowth settings. Listing 1 shows how to modify the transaction log autogrowth settings for the SampleDB database.

Listing 1: Change the autogrowth settings of SampleDB

ALTER DATABASE [SampleDB]

MODIFY FILE

(NAME = N'SampleDB_log', FILEGROWTH = 100MB, MAXSIZE = 200MB )

Expanding Transaction Log Manually

When the transaction log is automatically expanded, any transaction that requires a write to the log will have to wait while the log is expanded. This means transactions are delayed for however long it takes to expand the transaction log. This might be a minimal amount of time if the transaction log only grows a small amount.

You might not want the transaction log to grow automatically, especially during the middle of a busy day when users issue lots of transactions. To avoid holding up transactions while the log automatically expands, you can expand the transaction log manually during some scheduled off-hours of your choice. The script in Listing 2 shows how to use TSQL to expand the transaction log.

Listing 2: Increasing log file to 12 MB

USE [master]

ALTER DATABASE [SampleDB]

MODIFY FILE ( NAME = N'SampleDB_log', SIZE = 12MB)

The command in Listing 2 expands the SampleDB transaction log to 12 MB by modifying the transaction log file setting using the “SIZE” parameter.

Identifying the number of VLF’s in your Transaction Log

Each time the transaction log is expanded, more virtual log files (VLFs) are created and associated with the log file. If the transaction log has been expanded over time, by small increments of disk space, then a database will have more VLFs than if the log file has been expanded by large amounts. For SQL Server 2014 and above Table 1 shows the number of VLFs that will be added depending on the amount of space added to the transaction log.

Table 1: Number of VLFs added based on Growth Size of the Transaction Log

Number of VLFs	Growth Size
1	< 1/8 the size of the transaction log
4	< 64 MB and > 1/8 the size of the transaction log
8	>= 64 MB and < 1 GB and > 1/8 the size of the transaction log
16	>= 1 GB and > 1/8 the size of the transaction log

Is the growth size less than 1/8 the size of the current log size?
Yes: create 1 new VLF equal to the growth size
No: use the formula above

Here’s the formula:

8 VLFs from the initial log creation
All growths up to the log being 4.5 GB would use the formula, so growths at 1, 1.5, 2, 2.5, 3, 3.5, 4, 4.5 GB would each add 8 VLFs = 56 VLFs
All growths over 4.5 GB will only create 1 VLF per growth = (200 – 4.5) x 2 = 391 VLFs
Total = 391 + 56 + 8 = 455 VLFs

You can use the undocumented DBCC LOGINFO command to display the number of VLFs for a database. There is one row returned for each VLF. The script in Listing 3 will display the number of VLFs associated with the AdventureWorks2019 database.

Listing 3: Running DBCC LOGINFO against the AdventureWorks2019 database

USE AdventureWorks2019;

DBCC LOGINFO () WITH NO_INFOMSGS;

The results from my copy of the database are shown in Report 1.

Report 1: Output when the script in Listing 3 is run.

Because DBCC LOGINFO is undocumented, no official documentation defines what each of these columns means. Many people have written posts that describe the columns, so I have provided the descriptions I’ve found on the internet:

RecoveryUnitID – Added in SQL Server 2012, current unused
FileID – the FileID number as found in sysfiles
FileSize – the size of the VLF in bytes
StartOffset – the start of the VLF in bytes, from the front of the transaction log
FSeqNo – indicates the order in which transactions have been written to the different VLF files. The VLF with the highest number is the VLF to which log records are currently being written.
Status – identifies whether or not a VLF contains part of the active log. A value of 2 indicates an active VLF that can’t be overwritten.
Parity – the Parity Value, which can be 0, 64 or 128 (see the Additional Resources section at the end of this article for more information)
CreateLSN – Identifies the LSN when the VLF was created. A value of zero indicates that the VLF was created when the database was created. If two VLFs have the same number then they were created at the same time, via a growth event.

Manage Transaction Log Growth by taking Backups

If you have your database set to full or bulk-logged recovery mode, you need to periodically take transaction log backups to keep the transaction log from filling up. Depending on how you have your autogrowth setting for the transaction log, the log might just keep growing based on the FILEGROWTH and MAXSIZE setting. If you never take a take transaction log backups it might grow until it reaches the MAXSIZE setting or fills up the disk drive where the transaction log lives. To removed committed transaction log records from the log, all you need to do is take a transaction log backup.

To back up the transaction log you first need to make sure you have a full backup of the database. Once the full backup has been completed, a backup the transaction log can be taken, using a TSQL command similar to the backup command in Listing 4.

Listing 4: Transaction log backup command

BACKUP LOG [MyDatabase]

TO DISK = N'C:\MyBackups\MyDatabase.trn'

WITH NOFORMAT, NOINIT,

NAME = N'MyDatabase Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

Each time I run the backup command in Listing 4, a new transaction log backup will be added to the MyDatabase.trn file, and all inactive VLF’s will be removed from the transaction log. The frequency of taking transaction log backups depend on the amount of transactions performed against a database. The more updates performed, the more frequently you should run a transaction log backup. By running the transaction log frequently enough, you might be able to keep your transaction log from growing.

Monitoring the Transaction Log Usage

In order to monitor the size of the transaction log, the team at Microsoft have provided a dynamic view named “sys.dm_db_log_space_usage”. The code in Listing 5 shows how to use this view.

Listing 5: Showing how much used log space on master database

USE master;

SELECT * FROM sys.dm_db_log_space_usage;

When I run the command in Listing 5 on my instance, I get the output shown in Report 2.

Report 2: Amount of space used

By reviewing this report and looking at the used_log_space_in_percent column, you can see that just a little over 55% of my log space is used on my master database.

How to identify when an autogrowth Event Occurs

SQL Server creates an autogrowth event whenever a database file automatically grows. As of SQL Server 2005, the autogrowth events are included in the default trace. If you haven’t turned off the default trace, then the autogrowth events are simple to find, using SSMS or TSQL.

To view the autogrowth events in SSMS, right click on a database name in Object Explorer. In the context menu displayed, hover over the “Reports” item in the context menu, then move the mouse to hover over the “Standard Reports” and then select the “Disk Usage” report. Upon doing this for a database, a report similar to that shown in Report 3 will be displayed. To view the autogrowth events for the database selected, click on the “+” sign next to the “Data/Log Files Autogrowth/Autoshrink” item, as shown by the red arrow in Report 3.

Report to show autogrowth when managing SQL Server transaction log file size

Report 3: Disk Space Usage Report

Clicking the “+” sign displays any autogrowths if they have occurred. Report 3, shows the autogrowth events on my SampleDB database.

Report from default trace to show auto growth

Report 3: Autogrowth events on SampleDB

In Report 3 you can see both Log and Data autogrowth events. Using the SSMS method will show you only autogrowth events in the active file of the default trace for one database. If you want to review autogrowth events for all databases on a server, regardless of whether it is in active file of the default trace or an any of the default trace rollover files you can use a script similar to the one in Listing 6.

Listing 6: Reviewing autogrowth events in the default trace file

DECLARE @filename NVARCHAR(1000);

DECLARE @bc INT;

DECLARE @ec INT;

DECLARE @bfn VARCHAR(1000);

DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace

SELECT @filename = CAST(value AS NVARCHAR(1000))

FROM ::fn_trace_getinfo(DEFAULT)

WHERE traceid = 1 AND property = 2;

-- rip apart file name into pieces

SET @filename = REVERSE(@filename);

SET @bc = CHARINDEX('.',@filename);

SET @ec = CHARINDEX('_',@filename)+1;

SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));

SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));

-- set filename without rollover number

SET @filename = @bfn + @efn

-- process all trace files

SELECT

ftg.StartTime

,te.name AS EventName

,DB_NAME(ftg.databaseid) AS DatabaseName

,ftg.Filename

,(ftg.IntegerData*8)/1024.0 AS GrowthMB

,(ftg.duration/1000)AS DurMS

FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg

INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id

WHERE (ftg.EventClass = 92 -- Date File Autogrow

OR ftg.EventClass = 93) -- Log File Autogrow

ORDER BY ftg.StartTime

Knowing when, how often, and which databases have had autogrowth event occurs will help you identify when each database is growing. You can then use these time frames to determine which processes are causing your transactions logs to grow.