This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
SQL log file 2TB is not enough, now what?
Question
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?
asked 2019-10-18 by James Jenkins
Answer
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.
answered 2019-10-19 by Andy Mallon