In this article, we will go through a number of SQL Server Transaction Log recommendations and best practices that each database administrator needs to follow in order to enjoy his holidays and have a pleasant sleep without nightmares or calls during the night.

Before reading this article, it is recommended to check the previous articles of the SQL Server Transaction Log series (see the TOC below), in order to be familiar with the different SQL Server Transaction Log related concepts that you will meet in this article.

Transaction Log File Size

When a user database is created, the initial size and the auto-growth settings of the SQL Server Transaction Log file will replicate the initial size and auto-growth settings of the model system database, also known as Template database. By default, the SQL Transaction Log file initial size of a newly created database is 8MB, with the auto-growth amount of 64MB, as shown in the Files page of the Database Properties window ge below:

It is recommended to set the initial size and the auto-growth of the Transaction Log file to reasonable values. Although there is no one optimal value for Transaction Log File initial size and auto-growth that fits all situations, but setting the initial size of the SQL Server Transaction Log file to 20-30% of the database data file size and the auto-growth to a large amount, above 1024MB, based on your database growth plan can be considered as a good starting point for the normal workload. In this way, we will avoid the problem of the frequent and the small increments in the SQL Transaction Log file size, that may lead to queries slowness, due to pausing all log writing processes until the log file size increase process is completed, and generates a large number of Virtual Log Files. Keep in mind also that the Transaction Log file cannot take advantages from the Instant File Initialization option, that makes the Transaction Log file extending process slower.

Setting the SQL Server Transaction Log file initial size for the tempdb system database with a proper large amount should be considered from the beginning. This is due to the fact that, restarting the SQL Server instance will drop the tempdb data and log files and create it again with the original predefined initial size and auto-growth amount. If the SQL Transaction Log file of the tempdb is configured with small initial size, the extra overhead that is caused by increasing the size of the tempdb Transaction Log will affect the performance of the tempdb database.

Another factor that should be taken into consideration is the maximum size that the SQL Server Transaction Log file can reach. Two things should be considered when setting the maximum size amount; preventing the Transaction Log file from filling the disk drive and protecting the database from being inaccessible when the Transaction Log file runs out of free space, as follows:

After providing the proper values for the initial size, auto-growth, and maximum file size, we should keep an eye on the SQL Server Transaction Log file and monitor the database file growth while handling the incoming workload, and tune it based on that growth amount. The sys.dm_db_log_space_usage DMV can be used to monitor the log space in a specific database. It provides information about the amount of log space currently used and if the SQL transaction log needs truncation:

Note: For more information about the operations that cause the Transaction log file to grow and how to monitor and manage it, check SQL Server Transaction Log Growth Monitoring and Management.

Multiple Transaction Log Files

SQL Server provides us with the ability to create more than one SQL Transaction Log file on each database. But we should distinguish between the ability and the advantages of having more than one SQL Server Transaction Log file in your database. This decision is built mainly on the architecture of the Transaction Log file and the fact that the transaction logs are written in sequential order into the transaction log, without performing parallel I/O operations if there are multiple SQL Transaction Log files. In other words, the SQL Server Engine will keep writing to the first Transaction Log file until it has no free space, then it will start writing to the second one and so on.

Creating multiple SQL Transaction Log files has no enhancement to the queries performance but can help only if extending the Transaction Log file size is required but the current disk drive runs out of free space.

Number of VLFs

The Virtual Log Files, also known as VLFs, are the smallest logical units for storing the SQL Transaction Logs in the Transaction Log file.

The large number of VLFs in the Transaction Log file, that results from growing the file very frequently in small chunks every time a size extend is required, can lead to bad performance issues.

This can be seen in the shape of long recovery time during the SQL Server service startup, attaching the database or the backup and restore operations. This is due to the fact that, during the database recovery process, the SQL Server engine will read all the VLFs to roll forward the committed transactions and roll back the failed ones to keep the database in a consistent state.

Note: For more information about monitoring the number of VLFs in the SQL Transaction Log file and how to fix the large number of VLFs issue check What are SQL Virtual Log Files aka SQL Server VLFs.

Transaction Log File Location

As the sequential writing mechanism into the Transaction Log file differs from the random writing mechanism, using the proportional fill  algorithm, into the database data files, it is highly recommended to place the SQL Transaction Log files on a separated physical drive, not just store it on different partitions, in order to isolate the effect of the simultaneous sequential workload on the random workload. For Transaction Log file intensive writing workload, use Enterprise-grade SSD drives or minimum RAID 10 drive technology.

Recovery Model

The database recovery model option specifies how the SQL Server Engine treats the database transaction logs. SQL Server provides us with three recovery model types: Full, Bulk-logged and Simple recovery models.

Note: For more information about the differences between the recovery model types, check the SQL Server Transaction Log and Recovery Models.

The recovery model of any new database will be the same as the recovery model of the model system database, which is the Full recovery model by default. The decision of what recovery model should be set for any new database should be built on multiple factors. For example, whether this database participate in any database-level high availability solution such as Database Mirroring or Always On Availability Groups, how critical your data is and how frequent the data changes will specify if it is required to configure the database with Full or Bulk-Logged recovery model in order to provide point in time recovery for the database.

On the other hand, you need to consider the SQL Transaction Log monitoring and administration tasks that are required for the configured recovery model. The simplest example is configuring a Transaction Log backup when the database recovery model is set to Full or Bulk-Logged.

Transaction Log File Backup

When building a proper backup strategy for a production database, configured with Full or Bulk-Logged recovery model, you should ask your self-important questions: How much sensitive your business to the data loss? How frequent your data is changed?

Based on the answers of such questions, you can specify the frequency of the SQL Transaction Log backup and take advantages from the point in time recovery option that works only if you are taking Transaction Log backup using a proper backup strategy:

A Transaction Log backup is required also to truncate the inactive part of the Transaction Log to release it and make it available for reuse. If the SQL Server Transaction Log backup is not in your backup strategy and the daily Full backup is working fine with your database, change the recovery model of that database to Simple and the SQL Transaction Log will be truncated automatically after each Checkpoint.

Transaction Log File Shrink

In the SQL Transaction Log file Shrink operation, all free VLFs will be deallocated from the end of the Transaction Log file and returned back to the operating system, in order to reduce the physical size of the SQL Transaction Log file. The Shrink operation helps when the SQL Transaction Log file contains unused space or after performing a database operation that writes a large number of Transaction Logs.

Performing a SQL Transaction Log file shrink operation very frequently, or worse, enabling the Auto Shrink database option that shrinks the 25% of the Transaction Log file size is free, is NOT recommended. This is because you will not keep track of the normal growth of the Transaction Log file, and most probably the Transaction Log file will grow again after decreasing its size, causing performance degradation during the new space allocation that needs zeroing the space by the operating system before it is being allocated:

In the next article of the SQL Server Transaction Log series, we will show how to recover deleted or updated data from the transaction log file. Stay tuned!

Table of contents

• Author
• Recent Posts

Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen

Latest posts by Ahmad Yaseen (see all)

• Azure Data Factory Interview Questions and Answers - February 11, 2021
• How to monitor Azure Data Factory - January 15, 2021
• Using Source Control in Azure Data Factory - January 12, 2021