SQL SERVER – Removing Additional Transactional Log Files

The best part of writing blogs is that I always get follow up questions from users. Recently I wrote two blog posts while working on Comprehensive Database Performance Health Check about additional transactional log files and today I got to follow up on questions via email. Let us discuss that in today’s blog post.

To get the context of today’s blog post, please read the following two blog posts first:

• SQL SERVER – Multiple Log Files to Your Databases – Not Needed
• How to Solve Error When Transaction Log Gets Full? – Interview Question of the Week #272

While I was very clear in my blog posts that additional transactional log files do not increase performance, one of the blog readers still tried out this experiment on their production server, leading to the inspiration of this blog. Let us read the email I received this morning.

“Hi Pinal,

I followed your blog posts about how multiple log files are not recommended and decided to try them out on the production system. I added the log file on our busiest server and did various tests. It seems you are correct, our team did not see any performance improvement in over 2 hours of observation under heavy workload time.

Now afterward we tried to remove the file but we are not able to remove the file and it is giving us an error that the file cannot be removed because it is not empty.

Can you help us now, please? Also, I learned a lesson.”

Please note that I have removed the name of the person who sent me an email and not intending to publish it.

So first let us see the command to remove the log file.

USE [TestDB]
GO
ALTER DATABASE [TestDB] REMOVE FILE [NewLog]
GO

If your log file is empty when you run the above code, it will work just fine and remove the log file however if it gives the error displayed in the image below it means your log file contains the active data and it can’t be just removed by running the statement above.

The error will say:

Error 5042: The file ‘NewLog’ cannot be removed because it is not empty. 

Now let us try to overcome this error.

Solution / Workaround: Removing Additional Transactional Log

Now if the file is empty, your remove command will work fine. However, if it is not empty, you can follow the two steps process which I have listed below.

BACKUP DATABASE [TestDB] TO DISK = N'D:\data\delete1.bak'
GO
BACKUP LOG [TestDB] TO DISK = N'D:\data\delete.log'
GO

Right following it immediately run the command to remove the log file and it will work fine.

USE [TestDB]
GO
ALTER DATABASE [TestDB] REMOVE FILE [NewLog]
GO

Please note that some people say sometimes you do not have to take a full backup and only taking log backup will work. However, I have always taken the fullback in the scenario when I have to remove the log files. It is better to be safe than sorry if any unwanted accidents happen.

Reference: Pinal Dave (http://blog.SQLAuthority.com)