Whenever a user adds, delete, or edit any record in the SQL Server all the changes are maintained in the transaction log. In addition, the background process keeps on writing the each and every transaction in the log and to the database. After that, the transaction log is marked as written. There are two types of recovery model i.e. Simple and Full Recovery Model is used by the database in the SQL Server. However, if the database uses full recovery model all the written transaction logs are maintained in the log. Moreover, it becomes mandatory for the user to manage the transaction log file and take backup on the daily basis. In addition, the transaction log files are big enough if the full recovery model is used for the database. Apart from this, the Transaction logs in the SQL server are the always auto-growth and it is also advised not to turn off the auto-growth feature. Because it is helpful in the case of emergencies.
More About the SQL Server Transaction Log Size
The Transaction Log files in the SQL Server database is made up of one or more physical files and SQL Server writes to one physical transaction log file at a time. The internal structure of the physical files that is used by the SQL Server for transaction log files is known as Virtual Log Files (VLFs). However, the number and size of VLFs files inside the transaction log files directly depend on the number of factors. The size of VLFs files is determined at the time when the transaction log file is created or extended.
Effect of Large Transaction Log in SQL Server Performance
SQL Server transaction log file is comprised of small parts known as virtual log files whose size is not fixed. However, the main motive is to keep the small number of the virtual log files for the transaction log file. It is because SQL Server manages the smaller number of files more easily.
- If there are a huge number of virtual log files then, there can be two possible reasons behind this. One is the small transaction log that has grown up (manually or automatically) in very small segments and another is a problematic situation where the large growth segments were configured but accidentally small VLFs were configured in the Transaction log. However, if the Virtual Log Files grows unnecessarily large due to auto growth, the logs become fragmented and may results in delay also. Moreover, it also slows down the recovery process that is why having so many or very little virtual log files results in bad performance.
- Apart from all this, the auto-growth option is also offered, which is turned on by default. If the auto-growth settings are not handled in an appropriate manner, a SQL Server database will be forced to auto-grow that may lead to the serious performance issues. It is because the SQL Server will halt all processing until the auto-grow event is completed. However, the auto-growth event will take lots of space due to the physical organization of the hard drive that is not close physically to the previous one occupied by the transaction log file. This results in the physical fragmentation of the files that also causes slower response.
- It is always suggested to backup the transaction logs regularly. However, if the backup process fails, then the log files will grow largely and left with over-sized transaction file. It is because old transaction logs are not removed which makes transaction logs increase at a rapid rate. If the SQL Server database is having a large transactions logs then, it has a bad effect on the performance of the SQL Server such as:
- If the transaction log file is full in the SQL Server database, it degrades the performance of the SQL Server.
- It also slows down the speed of the transactional log backup process.
- In addition, the over-sized transaction logs decrease the disk space also because old transaction logs are not removed yet.
How to Resolve Over-sized Transnational Log Problem?
The number of VLFs files is increased by an auto-grow event, that has a common process but requires strict rules to prevent the unplanned issues with space or unresponsiveness. Therefore, to reduce the bad effect of over-sized transaction log files on the performance of the SQL Server, it is necessary to resolve the issue.
The most common solution is that reduce the number of virtual log files in the transaction logs files. Now, to do the same follow the three simple steps discussed below:
- Backup the Transaction Log files.
- After that, shrink the transaction log files.
It is because the number of virtual log files in the transaction logs is reduced by shrinking the SQL Server transaction log file that needs strict rules also to avoid deleting the data that has not been backed up till now.
Conclusion
Transaction Log files are the most important log files in any SQL Server database, which grows always because the auto-growth option is turn on by default. The internal structure of the transaction log file has many virtual log files. However, if there are an excessive number of VLFs then, it has some bad effects on the performance of SQL Server. Therefore, it is necessary to properly control the auto-growth feature of the transaction log files.
Alessandro Alpi's Blog 
Reblogged this on Cloud & Virtualization Complete Guide.
Hi there, we are experiencing poor performance in a large on SQL Server 2016. I came across your article when look for articles on log usage. In our case the log file is 75GB, yet only 1% is being used. The recovery model is simple. I cannot find any articles on log files being too small or issues with such low usage percentage. Do you have any recommendations for this? The log file and data file are on the same, which we will fix too. Many thanks
If the database is simple mode, the SQL Server transaction log won’t grow.
Anthony, that’s not entirely true. While yes, simple mode recovery will continually mark the log space as free when the system is not using it, SQL will still use the log file to store transactions while it’s using them. That’s how it maintains its integrity, for instance if the server suddenly goes down for its recovery. riffrack to pull this point to your situation, likely one of two things happened. Either your database was in full recovery, and you’ve changed it to simple which is why that space is suddenly free. Or you’ve been in simple all along, and SQL needed the log space at some point to write a large number of transactions. To the author’s point, shrinking the log will reduce the number of log files, but they’ll just be recreated as it grows again. The optimal solution is to shrink it then grow it to whatever size you expect it to need. As the blog suggests, small auto-grow settings are usually what causes the number of files to balloon up. If you want to see how many log files you have, just run a DBCC loginfo and count the number of rows. More details are here: http://adventuresinsql.com/2009/12/a-busyaccidental-dbas-guide-to-managing-vlfs/