SQL Server Delete Transaction Log File

Can I Delete SQL Server Transaction Log File?

We can not delete primary transaction log file, but we can delete secondary log files in SQL Server.

Sometimes we can create a second log file on another disk as a workaround because the log file is full.

If we do not delete these temporary secondary log files over time, log file count will increase.

When we have free space on the disk where the first log file is located, or when we move this first log file to another disk with sufficient space, we can delete these temporary secondary log files.

To see how a second log file is added to the database, you may want to read the article “How To Add The Second Log File to the Database”.

If your database’s recovery model is Full Recovery Model, I recommend that you backup your transaction log. So, your transaction log file automatically truncated after log backup and this prevents your transaction log to growing too much.

You may want to read the following articles to learn detailed information about recovery models and transaction log.

“What is Database Recovery Model“,

“What is SQL Server Transaction Log“,

“How To Shrink SQL Server Transaction Log”

Empty Secondary Transaction Log File Before Removing

After backing up the transaction log, we need to shrink the log file we want to delete with empty option.

First we right-click on the database and click Tasks-> Shrink-> Files.

We select the Log option from the File type section as shown below.

We choose the file we want to shrink from the file name.

In the Shrink action section, we also select “Empty file by migrating the data to other files in the same filegroup”.

In this way we are able to transfer the data in the log file to the other log files.

Before we click Ok, let’s take the script by clicking on the “Script” at the top left. This will output a script like the following.

Remove Secondary Transaction Log File

Right-click on the database and click Properties.

Go the “Files” tab on the left side and select the log file that you want to delete from the “database files” secion and click on the remove button at the bottom right. Finally, click “Script” on the upper side to get the remove secondary log file script.

This will output a script like the following.

You can remove the log file by combining these two scripts as follows.

After removing the secondary log file, it may appear that it has not been removed from the database for a while. Do not worry, this is normal. It will return to normal state after a while.