Saturday, April 13, 2024

More than one database transaction log file?

You can have more than one transaction log file for your database... but why would you?   

SQL Server will only write to one log file at a time, regardless of how many you give the database.  The transaction log file is written to sequentially, not serially, and there are ZERO performance benefits to having more than one log file for your database.  In fact, multiple log files can actually degrade your performance in some cases.  

But again, why would you have more than one log file?  Easy.  Your log file blows up due to a rogue transaction or some other unexpected reason, and your drive is filling up fast.  You cannot afford the downtime, so your only choice is to add a 2nd transaction log file temporarily.

Here's how:

USE master;
ALTER DATABASE Nautilus ADD LOG FILE (
        name = 'NewLogFilename',
        filename = 'D:\MSSQL\2017\Log\Nautilus_log_2.ldf',
        size = 1048MB,
        filegrowth = 5%
        )
GO

Now you can do whatever you need to do operationally until you reach a point where you can clean things up and remove that 2nd log file.  When it doesn't contain any transactions, the log file can be removed with this ALTER statement:

ALTER DATABASE Nautilus REMOVE FILE NewLogFilename;

If the log file is not completely empty, your statement may fail with this error:


The fast way to resolve that is to backup the log first:

BACKUP LOG Nautilus TO DISK = 'F:\Backup\Nautilus.bak'

Now run the same ALTER statement again and it should succeed:



We've got to remember that there is no reason to create more than one transaction log file for your database under normal circumstances.  The above method can be used in the abnormal or unexpected situations when you're running out of disk and need to do something fast to keep your database online.

See these for more details:   

Adding & removing data or transaction log files
Multiple transaction logs for SQL Server databases



No comments:

Post a Comment