0

This msdn link says that(https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver15#database-files):

The data and objects within the database spread across all six files, and the four log files contain the transaction log information.

I am trying to understand why would someone create 4 log files since having more than 1 log file doesn't improve performance.

5
  • 2
    Maybe this was just an educative purpose (to show that it can be done). Let's say you have your SQL server on a physical box with lots of physical disk, you could have your log spread over many disks... I know... who would do that :D Aug 11, 2021 at 18:21
  • Where is that example? Aug 11, 2021 at 20:55
  • David - in that link
    – variable
    Aug 12, 2021 at 3:24
  • downvoting as the link shows 4 data files and 1 log file Aug 12, 2021 at 8:13
  • The data and objects within the database spread across all six files, and the four log files contain the transaction log information.
    – variable
    Aug 12, 2021 at 8:23

1 Answer 1

2

The information in that article simply alludes to the fact that you can create more than one log file. As you've stated, and as reported in Microsoft's own documentation,

Having multiple log files in a database does not enhance performance in any way, because the transaction log files do not use proportional fill like data files in a same filegroup.

As for reasons why someone might have multiple log files:

  • You may have filled your log drive and need to add an additional log file on another drive to allow for additional growth.
  • You may be preparing for an activity that will cause substantial, but temporary, log growth. In this instance, capping your existing log file and adding a new one (on a new drive for example) can be a cleaner solution when you go to clean up the excess usage at a later date.
  • You may have limited space on high-performance disks, which is enough for your usual operational load, but you require additional space capacity for periodic workloads that can be on lower performance disks. In this instance, having your first log file limited and stored on the high-performance disks would give you the best performance during normal operational workloads, with space capacity during periodic workloads pushed to the additional log files on slower disks.

There are probably plenty of additional scenarios where additional log files could be useful, the key thing to remember is that additional log files do not aid performance, but are possible if required.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.