SQL SERVER – Multiple Log Files to Your Databases – Not Needed

The other day I was hired by a large consulting organization that is managing database operations for the bank Comprehensive Database Performance Health Check. The organizations wanted to have lots of performance issues and we worked through them one at a time. When we were done with the consulting work, I was asked a very interesting question by their DBA about log files. The question was – Is there ever need of multiple log files in SQL Server?

SQL SERVER - Multiple Log Files to Your Databases - Not Needed multiplelogfiles-800x267

Multiple Log Files and Performance

First of all, before you read this blog post, I must say that there is technically no need for multiple log files for your database. It does not improve the performance of help speed any of your queries at all. That said, I must also add that it is not necessary that by adding the additional log file, you will also degrade the performance. Performance is not directly related to the number of log files for the database. If there is an impact, I personally have not faced it as I do not practice multiple log files.

Log Files and Other Issues

Multiple log file actually creates significant issues when you recovery of your database happens as well as when you have to manage your database backups and storage.

The way SQL Server works is that it only writes to one log file at a time even though you have multiple log files. Additionally, all the writes are sequentially so when though there is a second log file available, it is going to stay ideal as all the writes will happen on the first file.

SQL Server will use the secondary log file only once it fills up the first log file and no there is no further space to write in the log file. This is the time it will write the data into the second log files.

Need for Second Log File

Personally, in my life, I have only added the second log file once when the first log file was in a disk which was filled up due to unrestricted growth of the log file and our database went into sudden stop mode where we were not able to anything at that time. We initially thought of emptying the log file but that was also not possible due to some of the active transactions.

During this time we immediately added the second log file on another disk and our database started to function properly. The very first task, which we did once our database recovered was to take a backup of the first log file and made some space inside it. We, later on, removed the secondary log file and our business was as usual.

As a proactive measure, we put the tracking and alter on the log file size as well as the available disk space so we can avoid such scenarios in the future.

Script to Add Second Log File

Even after reading all this you still want to add a second log file to your database, here is the script.

USE [master]
GO
ALTER DATABASE [TestDb] ADD LOG FILE ( NAME = N'NewLog', FILENAME = N'C:\NewLog.ldf')
GO

Once again, I would like to remind that there should not be any need of adding of Second log file in the normal circumstances.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – Fixing Freezing Activity Monitor
Next Post
SQL SERVER – Removing Additional Transactional Log Files

Related Posts

7 Comments. Leave new

  • Pinal, I think you could help some readers by clarifying that you are referring here to *transaction* logs. Some readers may not appreciate the distinction (from error logs, etc.). And even if they may, they may not realize it’s what you mean as the word transaction currently occurs only once, late in the article.

    Just adding that one word in the title and first sentence would solve it, though perhaps a sentence or two clarifying thier purpose would help ensure readers appreciate the rest of the concerns you raise.

    Of course, thanks for all you DO share.

    Reply
  • Hi Pinal,

    It’s not true, we have seen lots of improvement by adding additional log file, especially for TempDB, will see tremendous difference. Key factor is how many processors assigned to SQL server. I recommend number of processors assigned to SQL Server should be same as number of log file, at least for TempDB . Important thing is each log file should be on different drives otherwise disk R/W will become sequential and not advantage of additional log files.

    Regards,
    Mayura.

    Reply
    • This is absolutely incorrect and I strongly advise against it.

      What you are talking about is – Data file – MDF and not a Log File – LDF.

      Reply
    • I would like to see a test or link to the document where it says multiple log files are great.

      Again, not that I am talking about the Transaction Log file and not a Data files MDF or NDF.

      Reply
  • Hi Pinal,

    Yes, I am talking about Log File – LDF not Data file – MDF.

    I don’t have any document right now, will share if I get one. We have seen performance improvement after adding additional LDF file for all user databases and TempDB.

    Anyway I am big fan of you and appreciate your knowledge. I do follow most of your articles.

    Thanks,
    Mayura.

    Reply
    • Hi Mayura,

      I will be interested to see single documentation or a test which proves this one.

      In my career, I have never seen it and this will be a new discovery.

      Reply

Leave a Reply

Menu