7

This is a hypothetical question, that flows from the answer by sepupic where they explain that 2TB is the physical limit for SQL log file.

if you need it more than 2Tb you add the second log file.

I have long held the belief that multiple log files are bad as outlined in the post Multiple Transaction Log files and performance impact

I just can't imagine a scenario where having 2TB of log files even occurs, but if it does, and for some reason more frequent log backups will not cure it (multiple scenarios implied), what do you do?

Do you add a second log file, or is there something else?

0

3 Answers 3

8

multiple log files are bad

It's not that multiple log files are bad... It's that multiple log files are totally unnecessary and provide no benefit ... unless you require a log file that is larger than 2TB.

With data files, SQL Server can benefit from multiple files because it will perform parallel I/O to/from both files at the same time. However, with log files, SQL Server will only write to one or the other. The circular nature of the log file means that if you create two files, SQL Server will write to one, get to the end, then write to the other, get to the end, then go back to the first, etc.

There is no performance gain from having multiple log files.

However, because of the capacity limitation that any log file can be at most 2TB in size, if you need more than 2TB of log file, you would need to create multiple log files. This is the only reason to create multiple log files.

Why do you need a 2TB log file?

I just can't imagine a scenario where having 2TB of log files even occurs

Because your transaction log will grow during full backups, if you have a very large database that takes a log time to back up, and is very busy during backups, you may generate more than 2TB of transaction log during the backup.

Similarly, if you have a very large, log-running transaction (such as an index rebuild on a very large table), you may generate 2TB of transaction log before it can be re-used.

Of course, your transaction log can grow for a number of reasons. These aren't necessarily normal to make your log grow very large, but they do contribute to growth. If your transaction log backups are not completing successfully, or your AG is not synchronizing, or replication is not reading transaction from your log....... your log file will grow. If those problems continue, your log file will eventually reach 2TB.

That said, the need for 2+TB of log file is very rare.

1
  • 3
    +1, You might need to add a secondary log if the first one is completely full, and cannot be truncated, and the disk it resides on is full.
    – Hannah Vernon
    Oct 19, 2019 at 13:45
0

Microsoft recommends only one LDF file for each database.

This scenario is really very particular. However, you can perform the following operations:

1. Run the BACKUP LOG [database_name] WITH TRUNCATE_ONLY command
2. Run USE [database_name]
3. Run sp_helpdb 'database_name'
4. Write down the FileID of the log files.
5. For each file run the DBCC SHRINKFILE (FileID) command.

I hope I helped you :)

0
0

Do you add a second log file, or is there something else?

Alternatively, you just set reasonable autogrowth size and leave your log file grow.

There is no any control on it and log file can exceed 2Tb, you cannot set it explicitly but there is nothing that will prevent your log file growth, only disk free space.

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.