Ahmad Yaseen
SQL Server Transaction Log - DB File Properties

SQL Server Transaction Log Administration Best Practices

April 22, 2019 by

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:

SQL Server Transaction Log - DB File Properties

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:

Max Size of Transaction Log File

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:

sys.dm_db_log_space_usage result

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.

Num Of VLFs 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.

SQL Server Transaction Log - DB Recovery Model

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:

SQL Server Transaction Log - Data loss in the context of SQL Server database backups and Simple recovery mode

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:

auto-shrink

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

SQL Server Transaction Overview
SQL Server Transaction Log Architecture
What are SQL Virtual Log Files aka SQL Server VLFs?
SQL Server Transaction Log and Recovery Models
SQL Server Transaction Log and High Availability Solutions
SQL Server Transaction Log Growth Monitoring and Management
SQL Server Transaction Log Backup, Truncate and Shrink Operations
SQL Server Transaction Log Administration Best Practices
Recovering Data from the SQL Server Transaction Log
How to Rebuild a Database with a Corrupted or Deleted SQL Server Transaction Log File
Auditing by Reading the SQL Server Transaction Log

Ahmad Yaseen

Ahmad Yaseen

Ahmad Yaseen is a SQL Server database administration leader at Aramex International Company with a bachelor’s degree in computer engineering as well as .NET development experience.

He is a Microsoft Certified Professional with a good experience in SQL server development, administration, performance tuning, monitoring and high availability and disaster recovery technologies. Also, he is contributing with his SQL tips in many blogs

View all posts by Ahmad Yaseen
Ahmad Yaseen
Transaction log

About Ahmad Yaseen

Ahmad Yaseen is a SQL Server database administration leader at Aramex International Company with a bachelor’s degree in computer engineering as well as .NET development experience. He is a Microsoft Certified Professional with a good experience in SQL server development, administration, performance tuning, monitoring and high availability and disaster recovery technologies. Also, he is contributing with his SQL tips in many blogs View all posts by Ahmad Yaseen

3,264 Views