![\"Image](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/06\/word-image-64.png\")
<\/p>\nThe 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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
Growing in percentages does not scale well as the transaction log grows larger. It doesn\u2019t 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.<\/p>\n
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.<\/p>\n
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\u2019t 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.<\/p>\n
You can use SSMS to view the file growth settings for an existing database by reviewing the \u201cAutogrowth\/Maxsize\u201d settings. These settings are shown in the \u201dFiles\u201d section of the database properties pane, by right-clicking on a database and then selecting the \u201cDatabase Properties\u201d option from context menu displayed. Figure 1 shows my SampleDB using the \u201cIn Percent\u201d options for file growth with a \u201cMaximum File Size\u201d that is limited to 10 MB.<\/p>\n
<\/p>\n
Figure 1: Database Properties pane for SampleDB<\/strong><\/p>\n 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.<\/p>\n 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.<\/p>\n Listing 1: Change the autogrowth settings of SampleDB<\/strong><\/p>\n 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.<\/p>\n 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.<\/p>\n Listing 2: Increasing log file to 12 MB<\/strong><\/p>\n The command in Listing 2 expands the SampleDB transaction log to 12 MB by modifying the transaction log file setting using the \u201cSIZE\u201d parameter.<\/p>\n 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.<\/p>\n Table 1: Number of VLFs added based on Growth Size of the Transaction Log<\/strong><\/p>\n Number of VLFs<\/p>\n<\/td>\n Growth Size<\/p>\n<\/td>\n<\/tr>\n 1<\/p>\n<\/td>\n < 1\/8 the size of the transaction log<\/p>\n<\/td>\n<\/tr>\n 4<\/p>\n<\/td>\n < 64 MB and > 1\/8 the size of the transaction log<\/p>\n<\/td>\n<\/tr>\n 8<\/p>\n<\/td>\n >= 64 MB and < 1 GB and > 1\/8 the size of the transaction log<\/p>\n<\/td>\n<\/tr>\n 16<\/p>\n<\/td>\n >= 1 GB and > 1\/8 the size of the transaction log<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Here\u2019s the formula:<\/strong><\/p>\n 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.<\/p>\n Listing 3: Running DBCC LOGINFO against the AdventureWorks2019 database<\/strong><\/p>\n The results from my copy of the database are shown in Report 1.<\/p>\n Report 1: Output when the script in Listing 3 is run.<\/strong><\/p>\n \nBecause 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\u2019ve found on the internet:<\/p>\n 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.<\/p>\n 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.<\/p>\n Listing 4: Transaction log backup command<\/strong><\/p>\n 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\u2019s 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.<\/p>\n In order to monitor the size of the transaction log, the team at Microsoft have provided a dynamic view named \u201csys.dm_db_log_space_usage\u201d. The code in Listing 5 shows how to use this view.<\/p>\n Listing 5: Showing how much used log space on master database<\/strong><\/p>\n When I run the command in Listing 5 on my instance, I get the output shown in Report 2.<\/p>\n Report 2: Amount of space used<\/strong><\/p>\n By reviewing this report and looking at the used_log_space_in_percent<\/em> column, you can see that just a little over 55% of my log space is used on my master database.<\/p>\n How to identify when an autogrowth Event Occurs<\/strong><\/p>\n 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\u2019t turned off the default trace, then the autogrowth events are simple to find, using SSMS or TSQL.<\/p>\n To view the autogrowth events in SSMS, right click on a database name in Object Explorer. In the context menu displayed, hover over the \u201cReports\u201d item in the context menu, then move the mouse to hover over the \u201cStandard Reports\u201d and then select the \u201cDisk Usage\u201d 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 \u201c+\u201d sign next to the \u201cData\/Log Files Autogrowth\/Autoshrink\u201d item, as shown by the red arrow in Report 3.<\/p>\n Report 3: Disk Space Usage Report<\/strong><\/p>\n \nClicking the \u201c+\u201d sign displays any autogrowths if they have occurred. Report 3, shows the autogrowth events on my SampleDB database.<\/p>\n Report 3: Autogrowth events on SampleDB<\/strong><\/p>\n 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.<\/p>\n Listing 6: Reviewing autogrowth events in the default trace file<\/strong><\/p>\n 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.<\/p>\n The transaction log is a journal of update activity for a database. It can be used to back out incorrect or uncompleted transaction due to application or system issues. It also can be backed up so the transaction can be used to support point-in-time restores. The transaction log needs to be managed so it doesn\u2019t fill up. One way to keep the transaction log from filling up is transaction log backups periodically. Another way is to allow the transaction log to grow automatically as the transaction log needs additional space. DBAs must understand how the transaction is used and managed and how it supports the integrity of a database.<\/p>\nALTER DATABASE [SampleDB]\r\n\tMODIFY FILE\r\n\t\t(NAME = N'SampleDB_log', FILEGROWTH = 100MB, MAXSIZE = 200MB )\r\nGO<\/pre>\n
Expanding Transaction Log Manually<\/h2>\n
USE [master]\r\nGO\r\nALTER DATABASE [SampleDB] \r\nMODIFY FILE ( NAME = N'SampleDB_log', SIZE = 12MB)\r\nG<\/pre>\n
Identifying the number of VLF\u2019s in your Transaction Log<\/h2>\n
\n\n
\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
\n
USE AdventureWorks2019; \r\nGO \r\nDBCC LOGINFO () WITH NO_INFOMSGS;<\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/06\/word-image-65.png\")
<\/p>\n\n
Manage Transaction Log Growth by taking Backups<\/h2>\n
BACKUP LOG [MyDatabase] \r\nTO DISK = N'C:\\MyBackups\\MyDatabase.trn' \r\nWITH NOFORMAT, NOINIT, \r\nNAME = N'MyDatabase Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;\r\nGO<\/pre>\n
Monitoring the Transaction Log Usage<\/h2>\n
USE master;\r\nGO\r\nSELECT * FROM sys.dm_db_log_space_usage;<\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/06\/word-image-66.png\")
<\/p>\n![\"Report](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/06\/word-image-67.png\")
<\/p>\n![\"Report](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/06\/word-image-68.png\")
<\/p>\nDECLARE @filename NVARCHAR(1000);\r\nDECLARE @bc INT;\r\nDECLARE @ec INT;\r\nDECLARE @bfn VARCHAR(1000);\r\nDECLARE @efn VARCHAR(10); \r\n \r\n-- Get the name of the current default trace\r\nSELECT @filename = CAST(value AS NVARCHAR(1000))\r\nFROM ::fn_trace_getinfo(DEFAULT)\r\nWHERE traceid = 1 AND property = 2;\r\n \r\n-- rip apart file name into pieces\r\nSET @filename = REVERSE(@filename);\r\nSET @bc = CHARINDEX('.',@filename);\r\nSET @ec = CHARINDEX('_',@filename)+1;\r\nSET @efn = REVERSE(SUBSTRING(@filename,1,@bc));\r\nSET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));\r\n \r\n-- set filename without rollover number\r\nSET @filename = @bfn + @efn\r\n \r\n-- process all trace files\r\nSELECT \r\n ftg.StartTime\r\n,te.name AS EventName\r\n,DB_NAME(ftg.databaseid) AS DatabaseName \r\n,ftg.Filename\r\n,(ftg.IntegerData*8)\/1024.0 AS GrowthMB \r\n,(ftg.duration\/1000)AS DurMS\r\nFROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg \r\nINNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id \r\nWHERE (ftg.EventClass = 92 -- Date File Autogrow\r\n OR ftg.EventClass = 93) -- Log File Autogrow\r\nORDER BY ftg.StartTime<\/pre>\nManaging SQL Server transaction log size<\/h2>\n