none
Abnormal TRN log growth while DBs are in SIMPLE recovery mode RRS feed

  • Question

  •  Hello experts! 

    Our databases are in SIMPLE recovery mode and most of our transaction are set to perform BULK load using SSIS, even then we are hitting abnormal transaction log sizes on few databases. I see this is happening when there is overlap of our DB backup job with ETL jobs - I guess SQL server tries to record the transactions of ETL jobs while DB backup is being done.. to help recovery etc? I think the Bulk action is not working during the DB backup, hence the trn log is bulging.  Any tips to minimize  log growth and minimize the impact of overlap of workloads on each other? We have tight schedules /time window between DB Maint & ETL jobs.

    Platform:SQ 2008 R2 Enterprise

    Let me know if you need more information for an answer.

    Thanks in Advance!
    Mahesh
     
    Mahesh
    • Edited by Mahesh Dasari Wednesday, February 1, 2017 12:24 AM typos
    Wednesday, February 1, 2017 12:20 AM

Answers

  • Tibor,

    You are correct I read it in other way, meanwhile I found in BOL below snippet

    Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery. This topic identifies the operations that are minimally logged under the bulk-logged recovery model (as well as under the simple recovery model, except when a backup is running).

    So yes it seems like as per BOL minimal logging would not work as expected when backup is running.

    Did I beat you in giving the answer :P :)


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by Mahesh Dasari Thursday, February 2, 2017 5:43 PM
    Thursday, February 2, 2017 9:39 AM
    Moderator

All replies

  • Use the bulk logged recovery model.

    What happens when the backup starts it logs the lsn in the log and then backups up the data portion of your database. It then consults the lsn and backups the log from the lsn where the backup started to the lsn where the backup completed. This is stored in the database backup.

    The growth you see if the logged transactions from the bulk logged process which are stored in the log and the log cannot be "reclaimed" until the backup is complete.

    Wednesday, February 1, 2017 12:43 AM
  • "Use the bulk logged recovery model."

    To the best of my knowledge, there is no improvement in the bulk-logged recovery model compared to simple recovery model regarding log growth. Mahesh, please let us know if you see such, after your test (if you decide to test changing recovery model to bulk-logged).


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, February 1, 2017 12:25 PM
    Moderator
  • Hmm.. being developer I don't have the liberty to change recovery model,  unless there is a documented proof I may not be able to suggest/influence DBA.  I thought Simple model is superior/would be best of recovery models - in terms of performance of trn log management.


    Mahesh

    Wednesday, February 1, 2017 6:04 PM
  • I agree that simple should also allow for minimally logged operations, i.e., there should be no improvements compared to bulk logged. (Bulk logged has other benefits, the ability to do a log backup after a minimally logged operation, but that is not what we are talking about here).

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, February 1, 2017 6:26 PM
    Moderator
  • Hi, as you mentioned, if you have a database backup running at the same time as the ETL jobs, you'll have a performance issue. Possibly look at a solution where the ETL kicks of the backups once completed. Regarding keeping the TRN small, look at setting batch sizes for the bulk load, if you haven't already.
    Wednesday, February 1, 2017 6:34 PM
  • Hi, as you mentioned, if you have a database backup running at the same time as the ETL jobs, you'll have a performance issue. Possibly look at a solution where the ETL kicks of the backups once completed. Regarding keeping the TRN small, look at setting batch sizes for the bulk load, if you haven't already.

    Yes we are seeing performance issue and both the jobs getting impacted. We have scheduled the jobs in different time window but we have a dependency to kick start the ETL job when source feed arrive hence the job may start bit late and overlaps with backup job.

    I think bulk load is not happening or is automatically disabled and all transactions are fully getting logged during the backup process?; otherwise since we are using bulk load - shouldn't it do minimal logging, hence the trn log should remain smaller? 


    Mahesh

    Wednesday, February 1, 2017 6:57 PM
  • 'since we are using bulk load - shouldn't it do minimal logging, hence the trn log should remain smaller?'

    please see https://technet.microsoft.com/en-us/library/ms188267(v=sql.105).aspx

    'When you bulk import a large data file without specifying the batch size or any minimal-logging optimizations, the transaction log might fill before the bulk import operation is complete.'

    The caveat being, batch size does slow down the process.

    Wednesday, February 1, 2017 7:35 PM
  • 'since we are using bulk load - shouldn't it do minimal logging, hence the trn log should remain smaller?'

    please see https://technet.microsoft.com/en-us/library/ms188267(v=sql.105).aspx

    'When you bulk import a large data file without specifying the batch size or any minimal-logging optimizations, the transaction log might fill before the bulk import operation is complete.'

    The caveat being, batch size does slow down the process.

    I m expecting my SQL Server to perform minimal logged operation (but I think it is Not, due to backup operation); as I m meeting the criteria for "minimal-logging optimizations" when my DB is in Simple recovery model and use BULK INSERT or SELECT INTO operation in my ETL jobs?  Correct me if I m missing anything..

    Thanks.


    Mahesh

    Wednesday, February 1, 2017 9:35 PM
  • To be sure I'd suggest looking at the log_reuse_wait_desc in sys.database when you see the issue.

    This link may help https://technet.microsoft.com/en-us/library/ms345414(v=sql.105).aspx

    Wednesday, February 1, 2017 9:51 PM
  •  Hello experts! 

    Our databases are in SIMPLE recovery mode and most of our transaction are set to perform BULK load using SSIS, even then we are hitting abnormal transaction log sizes on few databases. I see this is happening when there is overlap of our DB backup job with ETL jobs 
    Mahesh
     
    Mahesh

    This is happening because log truncation are not going to happen when full backup is running, If i remember correctly a full backup would hold log truncation and would force log to grow. I am not sure whether changing recovery model would help but I am telling you the reason why log file grew. Could it not be possible to either change Bulk load time or backup database time. If you do so you might see the difference.

    Other option is, is it possible to limit amount of data being loaded ?


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, February 2, 2017 9:24 AM
    Moderator
  • Hi Shanky,

    I believe that we are talking about two different things here.

    1: Whether the log can be truncated during a full backup. I agree that it can't. But that is not the issue here:

    2: Whether minimally logged operations can be performed during full backup. I.e., does a concurrent full backup prohibit minimally logged operations, causing such operations like SELECT INTO etc to be fully logged while a full backup is being performed? This is the question in this thread, and I can't answer that off hand. I would have to investigate further (or ask), but at the moment I don't have those spare minutes. Possibly by (my) evening, unless you beat me to it. :-)


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, February 2, 2017 9:32 AM
    Moderator
  • Tibor,

    You are correct I read it in other way, meanwhile I found in BOL below snippet

    Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery. This topic identifies the operations that are minimally logged under the bulk-logged recovery model (as well as under the simple recovery model, except when a backup is running).

    So yes it seems like as per BOL minimal logging would not work as expected when backup is running.

    Did I beat you in giving the answer :P :)


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by Mahesh Dasari Thursday, February 2, 2017 5:43 PM
    Thursday, February 2, 2017 9:39 AM
    Moderator
  • Haha, good old Books Online. Yes Shanky, you sure did beat me! :-)

    Mahesh, did you see Shanky's reply? It seems you are in for re-arranging your scheduling...


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, February 2, 2017 9:49 AM
    Moderator
  • This topic identifies the operations that are minimally logged under the bulk-logged recovery model (as well as under the simple recovery model, except when a backup is running).

    So yes it seems like as per BOL minimal logging would not work as expected when backup is running.

     

    Thank you all; that confirms SQL behavior.  Yes, we are trying to re-arrange schedules and avoid any high volume loads during concurrent job run.

    Regards!

     

    Mahesh

    Thursday, February 2, 2017 5:49 PM