Post reply

Bulk Log reconvery model

  • May 21, 2012 at 2:02 pm

    #256158

    Bulk log recovery model minimize the log space.

    As per my knowledge we uses Bulk recovery model when we are using any bulk operation like BCP. Suppose I want to copy 1 million rows to a table using BCP or using any dml query. Each insert operation will be written to disk, so when 1 million rows will be copy or written to table, obviously the same number of entries written to the log file and thus log file will grow.

    Than how can we say that Bulk recovery model minimize log space ?

    Is this model bypasses any transaction operation or any similar operation to minimize log space?

    Can anyone explain me?

    Thanks

    Jitendra

  • May 21, 2012 at 4:09 pm

    #1490770

    http://www.sqlservercentral.com/articles/Recovery+models/89664/

    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

  • May 23, 2012 at 11:45 am

    #1492000

    jitendra.padhiyar (5/21/2012)

    Bulk log recovery model minimize the log space.

    As per my knowledge we uses Bulk recovery model when we are using any bulk operation like BCP. Suppose I want to copy 1 million rows to a table using BCP or using any dml query. Each insert operation will be written to disk, so when 1 million rows will be copy or written to table, obviously the same number of entries written to the log file and thus log file will grow.

    Than how can we say that Bulk recovery model minimize log space ?

    Is this model bypasses any transaction operation or any similar operation to minimize log space?

    Can anyone explain me?

    Thanks

    Jitendra

    "so when 1 million rows will be copy or written to table, obviously the same number of entries written to the log file and thus log file will grow."

    Hi Jitendra,

    The above statement is not true.....In Bulk Recovery model any BCP operations are minimally logged.....which means....it won't log row by row.....it will just say a summary like....."1 million rows were inserted', etc.......So, you cannot do point in time recovery......however, you are able to recover most of the items in case of a disaster....using BCM(Bulk Change Map).....

    Hope this helps.....if anyone else want to add/modify what I just said.....please do so.....I am here to learn as well.

    Regards,

    TA

    Regards,
    SQLisAwe5oMe.

  • May 23, 2012 at 11:49 am

    #1492004

    SQLCrazyCertified (5/23/2012)

    Hope this helps.....if anyone else want to add/modify what I just said.....please do so.....I am here to learn as well.

    Please read the article linked above.

    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 3 (of 3 total)

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