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