I have four SQL Server databases. Per the maximum load requirements, each database file can be between 1 and 2 TB in size. The HDD is a multi-disk DAS with about 9TB of space. During a database upgrade, the transaction log file for a single database can grow up to 4TB in size. (yeah, I added a new column to a multi-billion row table.) The databases are on Simple Recovery model.
I can find no recommendation (or rule of thumb) from Microsoft about how much disk space I need for a given database size. I get that it can vary widely. If my understanding is correct, it's the largest transaction which I need to consider since space internal to the log file is re-used for different transactions. I think the log growth is set to 10%, but even if it were set to 1 or 2 Gig increments, I think I'd still be cutting it close.
Is there some strategy I need to employ to avoid such huge log files or do I simply need more disk space to avoid running out of space during an upgrade? If I'm doing something wrong, I want to learn how to do it the proper way.
I'm using SQL Server 2012. Thanks!