SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Paul Randal / Bulk-Logged Mode / Latest Posts

RE: Bulk-Logged Mode

yogender — Mon, 09 Aug 2010 06:31:55 GMT

good question.

RE: Bulk-Logged Mode

KevinC. — Wed, 09 Jun 2010 14:42:20 GMT

Thanks for the excellent question, Paul. I am happy to say I learned this from you previously through your blog and I was able to get it right!Thanks again.

RE: Bulk-Logged Mode

Steve Jones - SSC Editor — Wed, 09 Jun 2010 10:32:17 GMT

Joe, thanks for the response, but I think you'd find many more people upset over a separate email than a short note.We'll likely continue to do this, but I do try to make sure I minimize it to a few places, and not too often.

RE: Bulk-Logged Mode

SanjayAttray — Wed, 09 Jun 2010 09:00:23 GMT

Good and easy but most imp. DBA interview question. In most interviews I was always asked pro's and con's of Simple, full and Bulk-logged recovery mode databases.

RE: Bulk-Logged Mode

WayneS — Tue, 08 Jun 2010 20:43:36 GMT

wrt the log file... I think the give-away here was the wording it "[i]will[/i] be [i]a lot[/i] smaller".This implies that it is 100% certain to be so - and in a multiple choice quiz, those are almost always the first to get thrown out.Have to admit though... I answered with trepidation. After all, a QotD from Paul... well, you never know exactly what you don't know until you handle one of his questions.(Hey Paul... when's Kimberly going to write some? :-D)

RE: Bulk-Logged Mode

Oleg Netchaev — Tue, 08 Jun 2010 14:53:54 GMT

Great question, it taught me something new today, thank you Paul! I got it wrong because I believed that log backups will be smaller (did not even notice [b]a lot[/b] word there), so I picked [b]Answers B and C[/b] option. The following statement in [url=http://msdn.microsoft.com/en-us/library/ms190692(SQL.90).aspx]this BOL page[/url] helped me to answer incorrectly: [quote]The bulk-logged recovery model... provides the best performance and least log space usage.[/quote] On the same page though it also states that log backups under bulk-logged model can be [quote]larger and take longer to complete than under the full recovery model[/quote], so option C was clearly not an option :(As far as the sales pitch is concerned, I actually like it, because though SSC and Ask.SSC are not the only Internet sites I visit, they are the only sites which I go to every day, and therefore, I would never even know about existence of the class mentioned in the explanation. Since it is in London, I cannot go of course, but if you had it anywhere near big ol' Texas I would be one of the first ones to register.Oleg

RE: Bulk-Logged Mode

Daniel Bowlin — Tue, 08 Jun 2010 12:24:15 GMT

Great question, thanks. I did a bit of reading to make sure my memory was correct about what was minimally logged and what was not, but then I tripped up on the log size.As far as the self promotional blurb at the end....SSC is one of the best free technical sites in the world. The community is strong, doesn't flame new comers for basic questions, and usually doesn't get very far off topic. I keep 4 tabs open in my browser all day at work, 1 is SSC and the other 3 are work sites. Although I think SSC would be substantially diminished if it were to go much more advertising supported, I think it is fair to allow the best contributors an occasional text paragraph with a link. I recognize the names of many of the regular contributors to this site; I recognize them as knowledgeable in the field and I would appreciate the opportunity to see one of them speak or teach if it were near by me. So, my vote would be to allow such basic self promotion as part of featured components of the site such as QOTD, Articles, and Editorials.

RE: Bulk-Logged Mode

UMG Developer — Tue, 08 Jun 2010 12:13:43 GMT

I have to say that I really appreciate the high-quality questions, and the follow-up responses that Paul provides. I have learned a lot from what he has provided.And the small blurb about his training courses in the QotD doesn't bother me at all. The site has ads all over, but I think the information and learning available are well worth the expensive of being exposed to the ads, since the site is totally free.

RE: Bulk-Logged Mode

Joe Howard — Tue, 08 Jun 2010 11:57:59 GMT

I appreciate your efforts, Steve, to encourage good questions and good explanations that help to educate, clarify and/or possibly intrigue us to check things out further. I also recognize you're part of a business to make a profit, and getting high caliber consultants to contribute for free is really a boon for you. But, there are plenty of other venues for distributing advertisements for pay-for events - e.g., blog postings, special emails to subscribers. The QoD is not one that comes quickly to mind.Paul, I am a consultant as well, so I recognize the cost of the time involved in putting good information together. I applaud your contribution along with all of the good (free) information that you've made available to all of us in several venues. Please keep up the good work. Maybe you can arrange with Steve to send out one advertisement email for each 2 or 3 QoDs you provide. That could include links to register, etc. I'd like that better.Joe

RE: Bulk-Logged Mode

Steve Jones - SSC Editor — Tue, 08 Jun 2010 11:38:16 GMT

[quote][b]Joe Howard (6/8/2010)[/b][hr]Good question, and I really appreciate the detailed explanation. I wish all the QoDs were so well explained. What I don't appreciate is the sales pitch at the end. Point us to your or other's blog postings or web sites, but don't try to sell us on attending a for-profit session or class. It turns the whole question into an advertisement, which are hard enough to avoid already.Joe[/quote]Joe,Sorry you feel that way. I made the offer to Paul to get some great questions and info from him as he's been a great supporter of SQLServerCentral with great posts and information over the years. His classes aren't that well known, so this is a way to let people know they're available.Many people do want to learn about opportunities that are out there for things like training.

RE: Bulk-Logged Mode

Paul Randal — Tue, 08 Jun 2010 11:19:40 GMT

Wrt the one line sales pitch at the end of my last two QOTDs, this is deal I worked out with Steve Jones. I provide a couple of really good QOTDs and get a one line blurb at the bottom. I've also provided one without any blurb too, or Steve asking, as a thank you.It takes about 1/2 hour to put together a well-worded, unambiguous question that a lot of people will benefit from. I don't see it as a hardship to have to endure a one-line, unobtrusive advert at the end. SQL Server Central is, after all, a business, even though it's totally free for everyone.I could further justify it by saying that almost 100% of the tons of info and time Kimberly and I provide to the SQL community is totally free and unencumbered with adverts (SQLskills.com is completely ad-free), so I don't feel bad about very occasionally doing something with a string attached. I don't really like saying that though.Thanks

RE: Bulk-Logged Mode

Paul Randal — Tue, 08 Jun 2010 11:09:34 GMT

@Festeron: Nope - the log backup containing the minimally-logged operation cannot be used for a STOPAT restore to any point in time covered by the entire log backup. For this reason, it makes to stay in BULK_LOGGED for as little time as possible and to bracket that time with log backups. You can restore (using a sequence of log backups) to any point before the start or after the end of that log backup, but not during it.Edit: Yes, you can see if a backup contains minimally-logged operations. In the output from RESTORE HEADERONLY, look for the HasBulkLoggedData column being 1, and in the backupset table in msdb, look for the has_bulk_logged_data field being 1.

RE: Bulk-Logged Mode

Festeron — Tue, 08 Jun 2010 11:00:55 GMT

[quote]What I don't appreciate is the sales pitch at the end.[/quote]+1

RE: Bulk-Logged Mode

Festeron — Tue, 08 Jun 2010 10:59:19 GMT

I got it right, but there's something unclear to me in the explanation:[quote]* A log backup that contains a minimally-logged operation cannot be used to do a point-in-time restore (i.e. you cannot use WITH STOPAT to a point-in-time covered by that backup). [/quote]Not even to a point-in-time that *precedes* the first minimally-logged operation?[quote]That backup can, of course, be used to stop at a point in time after the end of that backup.[/quote]It took some thinking to understand this "of course" point. While you cannot STOPAT a point-in-time covered by that backup, you *can* use that backup in a sequence of restores and STOPAT some later point.If I was given a set of transaction log backups, is there any way to inspect them to see which contain minimally-logged operations and which don't?

RE: Bulk-Logged Mode

Joe Howard — Tue, 08 Jun 2010 10:23:13 GMT

Good question, and I really appreciate the detailed explanation. I wish all the QoDs were so well explained. What I don't appreciate is the sales pitch at the end. Point us to your or other's blog postings or web sites, but don't try to sell us on attending a for-profit session or class. It turns the whole question into an advertisement, which are hard enough to avoid already.Joe

RE: Bulk-Logged Mode

Trey Staker — Tue, 08 Jun 2010 09:09:33 GMT

Excellent question and great explanation. Thanks!

RE: Bulk-Logged Mode

Paul Randal — Tue, 08 Jun 2010 08:14:11 GMT

[quote][b]lbowman (6/8/2010)[/b][hr]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.[quote]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.[/quote]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.[/quote]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.

RE: Bulk-Logged Mode

SQLAJ — Tue, 08 Jun 2010 08:05:36 GMT

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

RE: Bulk-Logged Mode

Hugo Kornelis — Tue, 08 Jun 2010 08:02:28 GMT

[quote][b]Paul Randal (6/8/2010)[/b][hr][quote][b]Hugo Kornelis (6/8/2010)[/b][hr]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.htmIs this a documentation bug?[/quote]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.[/quote]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.

RE: Bulk-Logged Mode

Paul Randal — Tue, 08 Jun 2010 07:48:21 GMT

[quote][b]Hugo Kornelis (6/8/2010)[/b][hr]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.htmIs this a documentation bug?[/quote]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.

RE: Bulk-Logged Mode

lbowman — Tue, 08 Jun 2010 07:03:21 GMT

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.[quote]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.[/quote]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.

RE: Bulk-Logged Mode

Atif-ullah Sheikh — Tue, 08 Jun 2010 03:53:57 GMT

Good one. Thanks.

RE: Bulk-Logged Mode

Kari Suresh — Tue, 08 Jun 2010 03:22:05 GMT

Good question.. Thanks

RE: Bulk-Logged Mode

Hugo Kornelis — Tue, 08 Jun 2010 01:50:16 GMT

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.htmIs this a documentation bug?

RE: Bulk-Logged Mode

free_mascot — Tue, 08 Jun 2010 01:43:43 GMT

Hello PaulPerfect Question!!! To be specific I like the Answers provided are superb.Anyway I pass it :-)Cheers

RE: Bulk-Logged Mode

sharath.chalamgari — Tue, 08 Jun 2010 00:55:39 GMT

Thanks Paul, i was thinking that the Log Backups will be smaller.again i learned something new today!

RE: Bulk-Logged Mode

Christian Buettner-167247 — Tue, 08 Jun 2010 00:55:20 GMT

Professional question & answer.Good start into the day.

RE: Bulk-Logged Mode

UMG Developer — Mon, 07 Jun 2010 22:45:39 GMT

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

RE: Bulk-Logged Mode

SQLRNNR — Mon, 07 Jun 2010 22:23:40 GMT

Nice question Paul.

Bulk-Logged Mode

Paul Randal — Mon, 07 Jun 2010 22:22:45 GMT

Comments posted to this topic are about the item [B]Bulk-Logged Mode[/B]