Database T-Log is full due to 'Active_transaction'. SQL server is using new T-log file first rather than old

Question

I had a SSIS process which failed due to T-log file Full for the Stage database. T-log file max growth is limited to 510GB however only 180-200 GB of t-log file space is getting used everyday. Even though T-log file had an enough space but it was failing with T-log FULL error. in order to troubleshoot I've added second T-log file (549 GB) to the new drive. My understanding is that SQL server will use first T-log file first and once it is reached to max limit then only it will start using 2nd T-log file however I noticed that it started using new T-log file rather than old first. It does not look like normal behavior. How does SQL server decide to use old T-log file or new T-log file first?

SQL server version:- SQL server 2012 SP4

enter image description here

• J.D.: Are you routinely backing up the transaction log? What does the log_reuse_wait_desc column say in sys.databases for that database?
• SqlDBA: It's a Simple recovery model. Log_reuse_Wait_Desc column says active transaction for the database. It's an active transaction that's a part of SSIS package process.
• Charlieface: How long has the transaction been open for? DBCC OPENTRAN WITH TABLERESULTS;
• SqlDBA: Transaction was open for about 100-120 min. I re-ran the package after adding new T-log file to the new drive and process completed successfully and it only used 182 GB of new T-log file space. I still don't know (1) Why process was failing with T-log full even though T-log file size is set to 500 GB ? (2) Why would SQL server start using new T-log file rather than old T-Log file first?
• Charlieface: That's a really long transaction. An open transaction will prevent the Transaction Log from truncating Can you not batch it up into sections?

Answer 1

Why process was failing with T-log full even though T-log file size is set to 500 GB

Is Replication or CDC enabled for the database?

select name, is_published, is_cdc_enabled
from sys.databases 

Why would SQL server start using new T-log file rather than old T-Log file first?

Because the old one was full.

• SqlDBA: Replication/CDC is not enabled for the database. Old T-log file was empty. I've added current file size image in original question. as we can see, original file was empty (33 GB) but SQL server started using second T-log (new one) file.