Rajendra Gupta
physical and logical architecture of SQL Server transactionn log

Drop unwanted (secondary) SQL Server transaction log files

January 31, 2020 by

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.

Database architecture

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:

Create a new database

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:

Specify multiple log files

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:

physical and logical architecture of SQL Server transactionn log

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:

Circular 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):

Monitor VLF status

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

  • Transaction log usage (used and free space)

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:

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

  • Transaction log backup

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):

VLF status

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:

Remove secondary log file

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:

Check removed log file

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

Verify log file SSMS

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:

Error message

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:

transaction log backup

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.

Rajendra Gupta
Maintenance, Transaction log

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

8,313 Views