Sticking with One Log File

We’ve had some discussions about multiple data files for a database. Do we get much benefit from setting up databases with multiple log files? Not likely. Let’s discuss why that is.

One and Done

If you already have multiple log files, it’s not good but also isn’t likely to be a source of any performance issues. You can have multiple log files configured but only one is going to be used at a time. The workload isn’t going to get spread out evenly across log files. One will handle the workload until it’s full and then the next will start being used.

I’d say the most common scenario for wanting or needing a second log file is for emergency situations when a log file is full. If you have one log file and it fills up, a possible fix can be adding a second log file on a different drive where space is available so that SQL Server can continue to function.

Home Alone

Let’s say we have two log files and want to make one disappear. This seems simple enough but can be a little bit tricky. Here are our files:

If we run:

ALTER DATABASE [ExampleDB] REMOVE FILE [ExampleDB_log2]
GO

We may get this message:

Msg 5042, Level 16, State 2, Line 7
The file ‘ExampleDB_log2’ cannot be removed because it is not empty.

To remove the additional file, you may first need to take a backup to empty it out. We can take a backup to empty the file and then try removing the file again:

Great! If we check our list of files now we’ll see that…the file is still there?

Let’s run the script again to make sure we removed the file:

Msg 5009, Level 16, State 9, Line 7
One or more files listed in the statement could not be found or could not be initialized.

This is actually to be expected. The file won’t be removed until the next log backup. If we take a backup and check again:

The file is no longer listed.

Thanks for reading!