I was doing some research on what the recommended maximum size for the transaction log, but I cannot find any substantiate or documented answer. I learned that a log size of about 25 - 50 percent of the DB is not uncommon. I also know that you want to set a recommended number of VLFs and must tune autogrowth to prevent VLF fragmentation. This can slow down your queries and log backups.
The only thing I cannot find is what would be a recommended maximum size in relation to your database size or possibly workload. Of course, you probably don't want your log file to grow beyond the database size, but what would be the exact impact? Of course, log backups will take longer if the log file is huge AND the log file is filled for example 80 percent.
But what if I have a db of 1 TB and a log file of (hypothetically :-) ) of 2 TB, which would be filled for 10 percent. I can imagine that log backups will take longer because the log backup has to scan the complete log maybe? If your VLFs are not fragmented, then there is not really a problem right?
Log backups are critical to SQL Server and let's say you would create a log disk that is 2 TB, make the log file 500 GB. Now, the backup fails for a few hours, the log fills for 1.5 TB. Your log backup will take longer, much longer, sure. We have a performance impact because of the autogrowth events, yes, but we made sure the DB does not stop. In such a scenario, when the backup is fixed and the first log backup has run, the log filing is low, but the log is unusually large. Now, I cannot think of any critical reason to get the log file back to the normal ( let's say that 500 GB). Of course you don't want to do that during production hours of course due to locking. However, I cannot imagine that there would be absolutely no impact with such a huge log file (which would be mainly empty).
To clear op my a little bit; my question is actually what is the impact of a scenario where a transaction log is huge (let's say 3x the size of the db) but log backups are taken regularly and it will normally be filled for only about 10%. Maybe slower log backups, possible really bad VLF fragmentation?
I don't want to do this. I only want to understand what the impact will be when I find a case where that is the issue (yes, there people who do this...). Of course I want to get it back to normal, but based on the impact, I can determine a priority. High; fix it now not matter what downtime, medium; fix it within a week because it is not critical, low; fix it during the next maintenance window.
Again; iam not interested in how to size it, but what would be the impact in a scenario where the file is already huge AND regular log backups are taken AND it is only filled for max 10% or so. Also, I want to be able to no only say that this is a bad, bad, bad, bad idea, but also why. Hope this helps to clear up my question. Btw, I tried to reproduce this scenario in my home lab, but it is difficult to see what the impact would be.