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?
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://darkslategrey-bat-805937.hostingersite.com)
