Post reply

Minimally Logged Operations in SQL Server 2005

  • July 17, 2007 at 2:06 pm

    #117778

    Hello!
     
    I am trying to confirm what BOL says ( http://msdn2.microsoft.com/en-us/library/ms191244.aspx):
    '...Bulk-logging is more efficient than full logging, and it reduces the possibility of a large-scale bulk operation filling the available transaction log space during a bulk transaction. However, if the database is damaged or lost when bulk logging is in effect, you cannot recover the database to the point of failure.

    Note:

    Unless a backup is running, minimal logging is used under the simple recovery model.
    ...'
     
    Does this mean that BULK-LOGGED recovery mode is more efficient in terms of logging when database backup is running?
     
    Thanks,
    Igor

  • July 17, 2007 at 3:28 pm

    #720178

    to avoid data loss you need to have full recovery model,as it logs the transactions fully....but the size will grow enormously in case of bulk insert operations......hence it is advisable to change the recovery to bulk logged recovery model if you are going to perform any huge bulk operations so that the log file growth will be minimal......but theres a possibility of data loss....

    [font="Verdana"]- Deepak[/font]

  • July 18, 2007 at 1:24 am

    #720284

    No infact when running with bulk logged mode your backup size will be much higher than the size it was when on full recovery mode. You can give a try and check on this.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • July 18, 2007 at 11:56 am

    #720474

    Sugesh,

    I am comparing IO footprint between bulk-logged and simple recovery modes. As BOL states:'...Unless a backup is running, minimal logging is used under the simple recovery model. '. That made me think that bulk-logged mode is more efficient when it comes to backup.

     

    Thanks,

    Igor

  • July 18, 2007 at 11:59 am

    #720476

    Deepak,

    My original questions was about perfomance/IO footprint of simple vs. bulk-logged modes it comes to backups. I do understand the implications on data loss.

     

    Thanks,

    Igor

     

  • July 18, 2007 at 12:15 pm

    #720486

    The only database backup operation that can be run when you are in SIMPLE is FULL DATABASE Backup. In BULK-LOGGED you can take TLOG Backups


    * Noel

Viewing 6 posts - 1 through 5 (of 5 total)

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