0

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

6
  • Are you routinely backing up the transaction log? What does the log_reuse_wait_desc column say in sys.databases for that database?
    – J.D.
    Commented Jan 1, 2021 at 0:57
  • 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.
    – SqlDBA
    Commented Jan 1, 2021 at 1:24
  • How long has the transaction been open for? DBCC OPENTRAN WITH TABLERESULTS; Commented Jan 1, 2021 at 2:56
  • 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?
    – SqlDBA
    Commented Jan 1, 2021 at 3:52
  • That's a really long transaction. An open transaction will prevent the Transaction Log from truncating Can you not batch it up into sections? Commented Jan 2, 2021 at 23:35

1 Answer 1

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.

1
  • 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.
    – SqlDBA
    Commented Jan 1, 2021 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.