6

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?

  • >>>I just can't imagine a scenario where having 2TB of log files even occurs<<< If you've listened about Kasperky they have enormous SQL Server databases in full model and have more than one log file for database, so their databases have log files that exceed 2Tb – sepupic Oct 18 at 12:10
  • Interesting! what about inside the transaction log? how many VLF would they have? what are the work load - high number of writes or massive transactions? – Marcello Miorelli Oct 18 at 12:18
  • @sepupic do you have the link for the Kasperky article? – Marcello Miorelli Oct 18 at 12:18
  • It's not an article, we just exchanged some messages on it (another sql server forum) – sepupic Oct 18 at 12:27
  • @sepupic how long does it take to backup a 2TB log file? If you are growing it faster than you can back it up, how do you manage that? – James Jenkins Oct 18 at 12:39
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.

  • 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. – Max Vernon Oct 19 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 :)

  • I always read that due to sequential writing to the transaction log file multiple files don't increase performance. Ok, agreed. But what about reading from multiple files? It should be faster, shouldn't it? – Denis Rubashkin Oct 18 at 12:29
  • @LeonardoLacerda your link to sqlskills is in the top voted answer by Aaron Bertrand, in the question I linked to in the third paragraph of my question. The backing up the log, is also implied in my question. Shrinking the log is generally not a good choice, as it will just grow again. My question is about managing a database where the log is at 2+TB and backing up the log more frequently is not the cure. – James Jenkins Oct 18 at 12:43
  • >>>But what about reading from multiple files?<<< Reading of log file is always sequential so you will read only one file at a time. Multiple log files are nor designed to any performance improvement, it's only an "emergency" way to add more space to a log that growed out of disk – sepupic Oct 18 at 12:43
  • @JamesJenkins Sorry, I didn't see the link was already within the question you linked. I Edited and removed the link so there are no redundancies. – Leonardo Lacerda Oct 18 at 12:48
  • >>>Reading of log file is always sequential so you will read only one file at a time<<< But besides me other processes (checkpoint, backup, AG/mirroring) could read the log in parallel. – Denis Rubashkin Oct 18 at 13:04
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, privacy policy and cookie policy

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