Post reply

advice on switching to bulk load revovery model

  • November 10, 2014 at 2:36 am

    #291796

    Hi

    Our production databases are in Full recovery model with transaction log backups occurring every 10 mins through 6am - 8pm. The typical size of our transaction log backups during production never gets above a couple of Mb (maybe 8Mb max).

    We do a full system backup at midnight.

    Around about 2pm there is a nightly load of data into the database. The first transaction log backup, which starts at 6am is usually around 4Gb which is eating up space on the L drive.

    Would it be worth switching the database in question to Simple or bulk logged recovery in order to stop the log file growing ? Our disc space is quite expensive from our supplier (we use cloud hosted vm's) and so I am trying to avoid asking for more disc for a process that only affects the log file size during the nightly loads ?

    Could this switch be made via a scheduled job, then another job to switch back just before production hours ?

  • November 10, 2014 at 2:52 am

    #1757372

    Switching from full to bulk logged or simple during ETL is totally possible and sometimes the best strategy.

    Please note that if some big transaction grows the log file during your ETL processing, you will need the disk space even in simple recovery, so make sure that it makes sense in your scenario.

    Also, you should take a full database backup right after switching to full recovery.

    -- Gianluca Sartori

  • November 10, 2014 at 3:44 am

    #1757383

    So how would I know if its just 1 big transaction that grows the log during the process or a combination of things - in which case the checkpoint will reset the log size if it was lots of individual transactions.

    I don't want to be in a situation whereby I have to go through our change process to make the change and it has no affect because the log grows anyway ? Can you advise ?

    Thanks

  • November 10, 2014 at 9:13 am

    #1757457

    I wrote an article on this site on bulk-logged recovery. Google should turn the link up easily enough. Have a read through that, should answer most of your questions.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply