This article explores the use of multiple SQL Server Transaction Log Files and the process of removing the secondary transaction log file.

Introduction

By default, a SQL Server database contains a primary data file and transaction log file. It is a good practice configuring multiple data files and split data across multiple data files. We can have these data files in separate storage drives to have multiple IO. It helps data management, improves performance, plans backup approaches according to filegroups.

We can have multiple transaction log files for a database as well.

SQL Server uses the multiple transaction log files in sequential order. Suppose we have two transaction log files. SQL Server uses one log file at a time and once it is full, SQL Server uses another log file. Since SQL Server does not use it in parallel, we do not get any performance benefit of it. Ideally, we should have only one transaction log file per database.

Let’s assume we have a production database, and we receive a critical alert that disk is running out of space. After investigation, we’ve found out that this drive holds a transaction log file. Due to active transactions, this SQL Server transaction log file is full. We’ve tried shrinking the log file, but it did not work. We back up the transaction log also, but due to active transactions, it could not release needed space. Adding another transaction log file in a separate disk with free space will resolve this issue.

As SQL Server uses serial mode for writing data in a transaction log file, we should remove the additional log file later. Let’s explore the process of removing an additional log file.

Create a database with multiple SQL Server Transaction Log files

Connect to a SQL instance in SQL Server Management Studio. Right-click on the Databases node in the Object Explorer pane and click on the New Database command:

It opens the New database window. Specify a database name and add another transaction log file. For testing purposes, we’ve disabled auto-growth of transaction log files. The initial size of the transaction log file is 8MB:

After everything is set, click the OK button to create a database in a default data/log file directory. Create a table and insert data into:

View the virtual log file status in SQL Server transaction log files

In the article, SQL Server Transaction Log Architecture, we explored the internal of the transaction log file. Each transaction log file consists of multiple virtual log files. A transaction log file is a combination of multiple virtual log files (VLF). The following screenshot shows the physical and logical architecture of the log file:

SQL Server stats a database with minimum VLF based on the initial log size and auto grow file (based on the auto-growth configuration). In the following image, we get a glimpse of the SQL Server transaction log file:

In the case of a single transaction log file, SQL Server uses a circular virtual log file path. We can check the number of virtual log files and their status using the following:

1. DBCC LOGINFO(‘Database’) – It is an old statement and works with all SQL Server versions
2. Dynamic management view sys.dm_db_log_info (DBID). It is available from SQL Server 2016 SP2 or later

Any of the commands can be used for the VLF check. For this article, we will use the dynamic management view (DMV):

In the above screenshot, we’ve verified that our sample database contains two transaction log files (file_id 2 and file_id 3).

• File_id 2 has active VLF (vlf_active=1 and vlf_status=2)
• File_id 3 does not have any active VLF (vlf_active=0 and vlf_status=2)

Let’s insert a few more records in the table so that active VLF will change:

The transaction log space usage monitoring and VLF status will be done with DMV (sys.dm_db_log_space_usage):

• VLF status

  1 2	SELECT DB_name() as DatabaseName,File_ID as transaction_log_file_ID, vlf_active , vlf_status FROM sys.dm_db_log_info(DB_ID('MultileLogFiles'));

• Transaction log usage (used and free space)

  1 2 3 4 5

  SELECT total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB, used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB, (total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024 AS free_log_space_in_MB FROM sys.dm_db_log_space_usage;

We get the following output of the above queries:

• On the left-hand side, we see that once the primary log file (file_id 2) becomes full, it moves to the next log file (file_id 3). At this point, we have active VLF in both primary and secondary log files. In a database with a full recovery model, we need a transaction log backup so that it marks VLF as inactive
• The right-hand side output (using DMV) shows the transaction log file used space 8.51 MB. As you recall, we’ve set 8 MB size for each transaction log file and we’ve disabled log auto-growth. When the primary log file is full (at 8 MB), SQL Server will switch to the secondary transaction log file

Remove secondary SQL Server transaction log file

To remove the secondary transaction log file (file id 3), we will use the edited Alter database statement.

We will add the REMOVE FILE clause and specify the file that we want to remove:

Execution of this statement will result in the following error message:

Note: The active transaction log file cannot be removed.

Previously, we saw that once the primary log file becomes full, SQL Server uses the secondary log file. We need to make a secondary transaction log empty, so we can remove it.

In the SQL database with a full recovery model, we use transaction log backups so SQL Server can truncate the logs. We might need multiple log backups for this depending upon transaction log size, active VLF and active transaction.

Let’s perform a full database and transaction log backup:

• Full database backup

  1	backup Database MultipleLogFiles to disk='C:\Temp\MultipleLogFiles.bak'

• Transaction log backup

  1	backup log MultipleLogFiles to disk='C:\Temp\MultipleLogFiles_log.trn'

Once the backup is completed, verify the VLF status. The active VLF should be in the first log file(primary) so that we can remove the secondary log file. We’ve verified that only the primary log file (file_id 2) is active ( VLF Status 2):

Now, the secondary transaction log file should be removed without any problems. Let’s execute the edited Alter database statement again. The secondary transaction log file will be removed:

Once the secondary transaction log file is removed, verify it using the GUI as well as T-SQL with system view sys.database_files:

The removed secondary transaction log file is still present as per the following screenshot:

Now right-click on the database and view existing files. We see the removed transaction log file in GUI as well. But why?

Let’s execute the edited Alter database statement again and see if SQL Server again removes the transaction log file.

We got the message that SQL Server could not find the specified log file:

SQL Server removes the transaction log file after a subsequent log backup. Let’s take another log backup and verify that the transaction log file still exists:

Verify the transaction log file in both GUI and T-SQL methods. We see that the removed transaction log file does not show up now.

Conclusion

In this article, we explored the usage of the secondary SQL Server transaction log and the process of removing it. You should avoid using multiple transaction log files, especially on the production database. You should take a database backup before planning any activity and do it in non-productivity hours.

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023
• Learn the PostgreSQL COALESCE command - January 19, 2023