Bulk-Logged Mode

  • 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

  • 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

  • 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).

  • Professional question & answer.

    Good start into the day.

    Best Regards,

    Chris Büttner

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

    again i learned something new today!

  • 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."

  • 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/

  • 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

  • Good one.

    Thanks.

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

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

  • 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

  • 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/

  • 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

  • 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

  • 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 29 total)

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