Bulk Logged recovery model

  • Comments posted to this topic are about the item Bulk Logged recovery model

    M&M

  • Excerpt from the link:"Under the bulk-logged recovery model, if a log backup covers any bulk operations, the log backup contains both log records and the data pages that were changed by bulk operations. This is necessary to capture the results of the bulk-logged operations. The incorporated data extents can make a log backup very large."

    yes it is correct then in many artical explained in bulk-logged recovery log file not getting high size, which is true?, i checked out , in bulklog recovery log file getting high size during the operation, then what is use of bulklog recovery..

  • "Now, after you take a log backup, could you see a log file bigger than usual?"

    No points for me. I perceive there being a difference between a "backup file" and a "log file". If someone says "log file" to me, I take it to mean the transaction log file, not the backup.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • The way I interpreted the question was:

    1. Database is in full-recovery mode

    2. User foresees bulk-operations (i.e. to be performed sometime in the future)

    3. User therefore changes the database to bulk-logged recovery mode

    4. User takes a log backup after changing the recovery model

    I was unable to find a step that indicates whether bulk-logged operations were actually peformed. As log as bulk-logged operations are not performed, the log file size would not be impacted if the backup was taken immediately after changing the recovery model.

    In my humble opinion, the question leaves room for an interpretation that's different than what the actual intention is.

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • I would go further than the other posters on this thread - sorry, this question and the answer given are just plain WRONG.

    See this MSDN blog entry which clearly explains log sizes in different modes:

    http://blogs.msdn.com/b/suhde/archive/2010/12/29/transaction-log-backup-size-in-bulk-logged-recovery-model.aspx

    The LOG FILE BACKUP is certainly likely to be larger.

    The LOG FILE (subsequent to backup) is almost certainly SMALLER given that it is not doing point in time restore.

    I will accept that the question could have been mangled unintentionally in the editorial prcess or something - but, the fact remains, the correct answer to the question as written is NO.

  • Nakul Vachhrajani (4/15/2012)


    The way I interpreted the question was:

    1. Database is in full-recovery mode

    2. User foresees bulk-operations (i.e. to be performed sometime in the future)

    3. User therefore changes the database to bulk-logged recovery mode

    4. User takes a log backup after changing the recovery model

    I was unable to find a step that indicates whether bulk-logged operations were actually peformed. As log as bulk-logged operations are not performed, the log file size would not be impacted if the backup was taken immediately after changing the recovery model.

    In my humble opinion, the question leaves room for an interpretation that's different than what the actual intention is.

    +1

    I'm waiting for Hugo opinion.

  • call.copse (4/16/2012)The LOG FILE (subsequent to backup) is almost certainly SMALLER given that it is not doing point in time restore.

    Hmm. Perhaps I'm missing something.

    Even if we are talking about the Log File... not the log backup... unless there was a subsequent Shrink Operation, it certainly will NOT be smaller. There is a fair chance it'll be the same size... and the question asks COULD it be bigger?

    It could be the same size, or bigger... but nothing says it HAS to be the same size (unless someone turned off autogrowth.)

    For me, I interpreted the question to be able to backup files of the transaction log... so I didn't even start thinking in these terms until I'd seen what you folks had written. 😉

  • What an horribly wrong question and answer!

    I agree with call.copse and with The CosmicTrickster: the question is plainly about the log file not the backup, but the answer is plainly about the backup, not the log file.

    Tom

  • Rich Weissler (4/16/2012)


    call.copse (4/16/2012)The LOG FILE (subsequent to backup) is almost certainly SMALLER given that it is not doing point in time restore.

    Hmm. Perhaps I'm missing something.

    Even if we are talking about the Log File... not the log backup... unless there was a subsequent Shrink Operation, it certainly will NOT be smaller. There is a fair chance it'll be the same size... and the question asks COULD it be bigger?

    It could be the same size, or bigger... but nothing says it HAS to be the same size (unless someone turned off autogrowth.)

    For me, I interpreted the question to be able to backup files of the transaction log... so I didn't even start thinking in these terms until I'd seen what you folks had written. 😉

    Interesting interpretation: "can the size of the log file at some time be smaller that its size at some earlier time" your interpretation would make sense, but with the wording of the question I would say that that interpretation probably requires treating it as a trick question. I suppose though that we should maybe remember that trick questions do turn up now and again and that some people use over-literal English which gives the impression of a trick question when no trick was intended.

    Tom

  • Thank you for the question. I thought the question was straightforward, but then it's Monday.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Sorry for the confusion. The question and answer was meant to test your knowledge on the log file backup size only and not the log file. Somehow the wording was not perfect thereby leading to wrong interpretation. Hope this clarifies.

    M&M

  • mohammed moinudheen (4/16/2012)


    Sorry for the confusion. The question and answer was meant to test your knowledge on the log file backup size only and not the log file. Somehow the wording was not perfect thereby leading to wrong interpretation. Hope this clarifies.

    .

    Don't worry. Writing questions is very difficult - everyone answering expects perfection, but no-one writing questions is perfect (certainly the ones I've written have been imperfect!). Just keep producing questions, they are very welcome even when we moan about them. We are all grateful to people like you who take the trouble to write questions - after all, most people can't be bothered to try writing them.

    Tom

  • L' Eomot Inversé (4/16/2012)


    mohammed moinudheen (4/16/2012)


    Sorry for the confusion. The question and answer was meant to test your knowledge on the log file backup size only and not the log file. Somehow the wording was not perfect thereby leading to wrong interpretation. Hope this clarifies.

    .

    Don't worry. Writing questions is very difficult - everyone answering expects perfection, but no-one writing questions is perfect (certainly the ones I've written have been imperfect!). Just keep producing questions, they are very welcome even when we moan about them. We are all grateful to people like you who take the trouble to write questions - after all, most people can't be bothered to try writing them.

    Thank you Sir.

    M&M

  • call.copse (4/16/2012)


    I would go further than the other posters on this thread - sorry, this question and the answer given are just plain WRONG.

    See this MSDN blog entry which clearly explains log sizes in different modes:

    http://blogs.msdn.com/b/suhde/archive/2010/12/29/transaction-log-backup-size-in-bulk-logged-recovery-model.aspx

    The LOG FILE BACKUP is certainly likely to be larger.

    The LOG FILE (subsequent to backup) is almost certainly SMALLER given that it is not doing point in time restore.

    I will accept that the question could have been mangled unintentionally in the editorial prcess or something - but, the fact remains, the correct answer to the question as written is NO.

    I would say that since the question read "...could you see a log file bigger than usual?" the only logical answer is yes. While the bulk-logged recovery model is a feature designed to reduce log file growth, it does not constrain log file growth. Therefore you certainly could see a larger log file in some circumstances.

    However, I would also say that that does not seem to be the intent of the question. The question was poorly worded, but the explanation is correct. So, poor wording of the question has given us a "If False then True" situation -- which, if my memory of basic logic serves me properly, is still True. So much for logic.

  • The CosmicTrickster (4/15/2012)


    "Now, after you take a log backup, could you see a log file bigger than usual?"

    No points for me. I perceive there being a difference between a "backup file" and a "log file". If someone says "log file" to me, I take it to mean the transaction log file, not the backup.

    I saw could... Could you see a larger backup file.... the correct answer... well it depends... did those bulk activities you forsaw actually occur? Was there a larger amount of normal database traffic that might have caused the log file to grow? Did somebody decide to rebuild all the indexes in the database?

    I mean really... Could that file, or the backup, or whatever have grown? Sure....



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

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

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