An examination of bulk-logged recovery model

  • Comments posted to this topic are about the item An examination of bulk-logged recovery model

    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
  • Thanks Gail.

    Well worth the read!

  • Thank you Gail, your article has really helped further my understanding of this rather murky area. It's great that you and other experts take the time out to help the community, it really is appreciated.

  • Awesome!

    Thank you, Gail, for taking the time to write such a thorough, detailed article.

  • Superb Article...


    Kind Regards,

    Chintak Chhapia

  • Thank you Gail for the excellent article.

    I do have one question: in the final demo where you looked at the log usage for an index rebuild, you mentioned "log reservation in case of rollback". Could you clarify what you mean by this?

  • ==== ( http://www.fullmalls.com ) =====

    ==== ( http://www.fullmalls.com ) =====

    $33 True Religion jeans, Ed Hardy jeans,LV,Coogi jeans,Affliction jeans

    $30 Air Jordan shoes,Shox shoes,Gucci,LV shoes

    50%Discount winter fashion :Sandle,t-shirt,caps,jerseys,handbag and brand watches!!!

    $15 Ed Hardy ,LV ,Gucci Bikini

    $15 Polo, Ed Hardy, Gucci, LV, Lacoste T-shirts

    $25 Coach,Gucci,LV,Prada,Juicy,Chanel handbag,

    $10 Gucci,Ed Hardy sunglasses

    $9 New Era caps.

    give you the unexpected harvest

    ==== ( http://www.scnshop.com ) =====

    ==== ( http://www.scnshop.com ) =====

    ==== ( http://www.fullmalls.com ) =====

    ==== ( http://www.fullmalls.com ) =====

    ==== ( http://www.fullmalls.com ) =====

    ==== ( http://www.fullmalls.com ) =====

  • ==== ( http://www.fullmalls.com ) =====

    ==== ( http://www.fullmalls.com ) =====

    $33 True Religion jeans, Ed Hardy jeans,LV,Coogi jeans,Affliction jeans

    $30 Air Jordan shoes,Shox shoes,Gucci,LV shoes

    50%Discount winter fashion :Sandle,t-shirt,caps,jerseys,handbag and brand watches!!!

    $15 Ed Hardy ,LV ,Gucci Bikini

    $15 Polo, Ed Hardy, Gucci, LV, Lacoste T-shirts

    $25 Coach,Gucci,LV,Prada,Juicy,Chanel handbag,

    $10 Gucci,Ed Hardy sunglasses

    $9 New Era caps.

    give you the unexpected harvest

    ==== ( http://www.scnshop.com ) =====

    ==== ( http://www.scnshop.com ) =====

    ==== ( http://www.fullmalls.com ) =====

    ==== ( http://www.fullmalls.com ) =====

    ==== ( http://www.fullmalls.com ) =====

    ==== ( http://www.fullmalls.com ) =====

  • ==== ( http://www.fullmalls.com ) =====

    ==== ( http://www.fullmalls.com ) =====

    $33 True Religion jeans, Ed Hardy jeans,LV,Coogi jeans,Affliction jeans

    $30 Air Jordan shoes,Shox shoes,Gucci,LV shoes

    50%Discount winter fashion :Sandle,t-shirt,caps,jerseys,handbag and brand watches!!!

    $15 Ed Hardy ,LV ,Gucci Bikini

    $15 Polo, Ed Hardy, Gucci, LV, Lacoste T-shirts

    $25 Coach,Gucci,LV,Prada,Juicy,Chanel handbag,

    $10 Gucci,Ed Hardy sunglasses

    $9 New Era caps.

    give you the unexpected harvest

    ==== ( http://www.scnshop.com ) =====

    ==== ( http://www.scnshop.com ) =====

    ==== ( http://www.fullmalls.com ) =====

    ==== ( http://www.fullmalls.com ) =====

    ==== ( http://www.fullmalls.com ) =====

    ==== ( http://www.fullmalls.com ) =====

  • ==== ( http://www.fullmalls.com ) =====

    ==== ( http://www.fullmalls.com ) =====

    $33 True Religion jeans, Ed Hardy jeans,LV,Coogi jeans,Affliction jeans

    $30 Air Jordan shoes,Shox shoes,Gucci,LV shoes

    50%Discount winter fashion :Sandle,t-shirt,caps,jerseys,handbag and brand watches!!!

    $15 Ed Hardy ,LV ,Gucci Bikini

    $15 Polo, Ed Hardy, Gucci, LV, Lacoste T-shirts

    $25 Coach,Gucci,LV,Prada,Juicy,Chanel handbag,

    $10 Gucci,Ed Hardy sunglasses

    $9 New Era caps.

    give you the unexpected harvest

    ==== ( http://www.scnshop.com ) =====

    ==== ( http://www.scnshop.com ) =====

    ==== ( http://www.fullmalls.com ) =====

    ==== ( http://www.fullmalls.com ) =====

    ==== ( http://www.fullmalls.com ) =====

    ==== ( http://www.fullmalls.com ) =====

  • Great article Gail.

    One doubt though - you mentioned that if we do a log backup when a minimally logged operation is running the log backup contains the "image" of pages being modified by the minimally logged operation.

    Will this increase the log file size or only the log backup size?

    Thanks

    "Keep Trying"

  • matt.bowler (5/7/2012)


    I do have one question: in the final demo where you looked at the log usage for an index rebuild, you mentioned "log reservation in case of rollback". Could you clarify what you mean by this?

    Sure.

    High level summary: If an operation is rolled back, SQL performs the undo by reading the transaction log and generating compensating operations to reverse the effects of whatever is being rolled back. Those operations, like any other data modification, get logged.

    A rollback cannot be allowed to fail if say the rollback is caused by a full transaction log, that would result in the database being marked suspect, which is a very bad thing.

    Hence, while SQL is performing any data modification and logging the changes, it also reserves enough space in the log to be able to log the undo of those operations. This is log reservation. The estimation that SQL uses is a very conservative one, it's better to reserve more log space than is actually needed than to not reserve enough and send the DB suspect.

    http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-25-Why-isnt-my-log-backup-the-same-size-as-my-log.aspx

    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
  • ChiragNS (5/8/2012)


    Will this increase the log file size or only the log backup size?

    Just the log backup. The pages don't go anywhere near the log file itself, they're just copied into the log backup when it is taken.

    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
  • Thanks Gail, great article.

    I thought I had a decent grasp of logging issues, but I must admit that I had a light-bulb moment when you described the Eager Write mechanism!

  • Ewald Cress (5/8/2012)


    I thought I had a decent grasp of logging issues, but I must admit that I had a light-bulb moment when you described the Eager Write mechanism!

    Please note that Eager Writes are just for minimally logged operations in bulk-logged or simple recovery, and they just write the minimally logged pages to the data file.

    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 15 posts - 1 through 15 (of 32 total)

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