In a SQL server, having multiple log files does not have any performance benefits as the log files are written sequentially. The log files can have external (disk level) fragmentation or internal (VLF, Virtual Log File) fragmentation. The external fragmentation can be solved by de-fragmenting the disk on which the log file resides on, followed by properly sizing the log files, to prevent further fragmentation.

Internal fragmentation is caused by the growing and shrinking of the log files. There is a nice article by Thomas LaRock on how to use powershell to get an output of the number of VLFs for your DBs. A basic rule of thumb that I use is, if there are more than 50 VLFs for a DB, it is time to truncate it. Using dbcc loginfo(‘db_name‘) with no_infomsgs will return one row for each VLF for db_name. To ‘de-fragment’ the VLFs, do the following:

1.  Backup the log file (when in full mode), or issue a checkpoint (when in simple mode).
2. Truncate the log file: DBCC SHRINKFILE (‘db_logfile_logical_name‘). Should be run in the context of the DB concerned.
3. Size the log file appropriately. ALTER DATABASE ‘db_name‘ MODIFY FILE (‘db_log_file_logical_name‘,new_size_as_int)

Do a DBCC loginfo again to see the reduced number of VLFs you now have.