Bulk-Logged Mode

  • Paul Randal

    One Orange Chip

    Points: 29438

    Comments posted to this topic are about the item Bulk-Logged Mode

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • SQLRNNR

    SSC Guru

    Points: 281210

    Nice question Paul.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • UMG Developer

    SSChampion

    Points: 13482

    Great question, thanks Paul!

    I'm really surprised at the number of people that think the log backups would be smaller. The logs files themselves yes, which is really the only reason I can see using the bulk-logged recovery model. It seems you either need complete disaster recovery (FULL) or you don't (SIMPLE).

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    Professional question & answer.

    Good start into the day.

    Best Regards,

    Chris Büttner

  • sharath.chalamgari

    SSCertifiable

    Points: 5680

    Thanks Paul, i was thinking that the Log Backups will be smaller.

    again i learned something new today!

  • free_mascot

    One Orange Chip

    Points: 27168

    Hello Paul

    Perfect Question!!!

    To be specific I like the Answers provided are superb.

    Anyway I pass it 🙂

    Cheers

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Good question, Paul!

    In your explanation, you write that log backups are about the same size as when full recovery had been uses. This matches my expectation, but in Books Online, it says:

    "The tradeoffs are bigger log backups and (...)"

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8cfea566-8f89-4581-b30d-c53f1f2c79eb.htm

    Is this a documentation bug?


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Kari Suresh

    Hall of Fame

    Points: 3712

    Good question.. Thanks

    KSB
    -----------------------------
    Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

  • Atif-ullah Sheikh

    SSChampion

    Points: 12495

    Good one.

    Thanks.

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • lbowman

    Default port

    Points: 1495

    Based on the explanation, the use of the term "a lot smaller" must be the reasoning for excluding answer "D". Even though http://msdn.microsoft.com/en-us/library/aa173529(SQL.80).aspx states.

    The Bulk-Logged Recovery model provides protection against media failure combined with the best performance and minimal log space usage for certain large-scale or bulk copy operations.

    Though, as pointed out, the data extents are logged, insufficent detail is logged to allow point in time recovery which would indicate that "significant" data is not written for BULK_LOGGED operations and therefore smaller log backups.

  • Paul Randal

    One Orange Chip

    Points: 29438

    Hugo Kornelis (6/8/2010)


    Good question, Paul!

    In your explanation, you write that log backups are about the same size as when full recovery had been uses. This matches my expectation, but in Books Online, it says:

    "The tradeoffs are bigger log backups and (...)"

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8cfea566-8f89-4581-b30d-c53f1f2c79eb.htm

    Is this a documentation bug?

    Hey Hugo - in the majority of cases I've seen, the log backups are a little smaller. I can easily think of cases where they'd be a bit larger (e.g. with a 5000-byte row, meaning 3000 bytes of wasted space per page being propagated into the backup - which wouldn't happen with regular logging. I would say that's a doc bug, yes.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Paul Randal (6/8/2010)


    Hugo Kornelis (6/8/2010)


    Good question, Paul!

    In your explanation, you write that log backups are about the same size as when full recovery had been uses. This matches my expectation, but in Books Online, it says:

    "The tradeoffs are bigger log backups and (...)"

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8cfea566-8f89-4581-b30d-c53f1f2c79eb.htm

    Is this a documentation bug?

    Hey Hugo - in the majority of cases I've seen, the log backups are a little smaller. I can easily think of cases where they'd be a bit larger (e.g. with a 5000-byte row, meaning 3000 bytes of wasted space per page being propagated into the backup - which wouldn't happen with regular logging. I would say that's a doc bug, yes.

    Thanks, Paul.

    I think the documentation writer meant to say that the log backups are bigger than the (active part of the) log file, but omitted to state that explicitly.

    I have submitted this as feedback to this BOL page.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • SQLAJ

    Ten Centuries

    Points: 1299

    Thanks for the question! It helps to reinforce/challenge knowledge. Currently reviewing MCTS 70-432 information so it was a perfect question!

    Cheers!

    ______________________________
    AJ Mendo | @SQLAJ

  • Paul Randal

    One Orange Chip

    Points: 29438

    lbowman (6/8/2010)


    Based on the explanation, the use of the term "a lot smaller" must be the reasoning for excluding answer "D". Even though http://msdn.microsoft.com/en-us/library/aa173529(SQL.80).aspx states.

    The Bulk-Logged Recovery model provides protection against media failure combined with the best performance and minimal log space usage for certain large-scale or bulk copy operations.

    Though, as pointed out, the data extents are logged, insufficent detail is logged to allow point in time recovery which would indicate that "significant" data is not written for BULK_LOGGED operations and therefore smaller log backups.

    There is a *lot* less written to the transaction log. The data extents are *not* logged at all - they are included in the next log backup - so the log backup contains all the information necessary to reconstitute the operation. If the log backups only included what was written to the log, they would be a lot smaller than in the FULL recovery model, but they'd also be useless. Hence log backups containing minimally-logged operations are roughly the same size as if the operation was fully logged.

    Try it out for yourself and you'll see.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Trey Staker

    SSCarpal Tunnel

    Points: 4736

    Excellent question and great explanation. Thanks!

    ---------------------------------------------------------------------
    Use Full Links:
    KB Article from Microsoft on how to ask a question on a Forum

Viewing 15 posts - 1 through 15 (of 30 total)

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