Does adding an empty column to a table increase the transaction log size in SQL Server?
Sometimes.
If you are adding a NOT NULL column to a table with a default constraint, it might be metadata-only and not use significant log size. If you are using SQL Server 2012+, and Enterprise Edition, then adding the column may be an online metadata-only operation.
Similarly, adding a NULL column without a default constraint may be an online meta-data-only operation.
Sometimes not.
Prior to SQL Server 2012 when you add a new NOT NULL column with a default constraint to an existing table, every row in the table is updated to add the default value of the new column--resulting in a fully logged size-of-data operation for the table. This is also true for 2012+ if not using Enterprise or Developer editions when adding a NOT NULL column.
If the table contains any SPARSE columns, that will prevent the add column from being online in any circumstance (even if normally allowed in a scenario described above). Certain other deprecated features/data types may also prevent adding the column as metadata-only--the nature of deprecated features means they aren't necessarily considered when adding new features, so they may or may not be compatible.
Similarly, if you are using Replication or Change Tracking, the configuration of those "keep track of all my changes" technologies may prevent the change from being online. If you're using any of them, you'll want to check & test.
Is it possible to calculate the required transaction log size knowing the data type and the number of rows in advance?
All schema changes will use some transaction log.
If adding the column qualifies for a metadata-only operation, then the amount of transaction log use would be minimal.
If adding the column does not qualify for a metadata-only operation, then the transaction will be a size-of-data operation--meaning SQL Server will essentially re-write the table, fully logged, in a single transaction, and require the transaction log space to do that.
Additionally, because the size-of-data operation may be long-running, SQL Server will not be able to free for reuse any portion of the transaction log while that transaction is running. If adding the column takes 2 hours, your transaction log will need to be large enough to hold both the data related to that schema change, and also every other transaction that runs concurrently with your schema change, following the normal log reuse rules. Note that even in Simple Recovery, SQL Server will only clear & reuse the transaction log prior to the start of the oldest open transaction--the DDL and all concurrent activity will be retained in the log until the DDL statement finishes.
Therefore, the amount of transaction log needed is the sum of both your size-of-data schema change AND everything else running at the same time. This means that testing in a non-production environment can cause you to under-estimate transaction log usage, because the lack of concurrent workload can affect both the amount of logged work, as well as the total duration.
If you are only going to be performing the schema change on a database with zero concurrency, you will find testing to provide a decent estimate--but even then an exact calculation is difficult due to the number of factors that can vary for logically identical data (size on disk, fragmentation, low page fill/page density, etc).
