Bulk-Logged Mode

Question

Bulk-Logged Mode

Viewing 15 posts - 16 through 29 (of 29 total)

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

Joe Howard SSCommitted Points: 1637 More actions · Answer 1

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

Festeron SSC Eights! Points: 927 More actions · Answer 2

I got it right, but there's something unclear to me in the explanation:

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

Not even to a point-in-time that *precedes* the first minimally-logged operation?

That backup can, of course, be used to stop at a point in time after the end of that backup.

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?

Festeron SSC Eights! Points: 927 More actions · Answer 3

What I don't appreciate is the sales pitch at the end.

+1

Paul Randal One Orange Chip Points: 29544 More actions · Answer 4

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

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 One Orange Chip Points: 29544 More actions · Answer 5

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

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

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 6

Joe Howard (6/8/2010)
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

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.

Joe Howard SSCommitted Points: 1637 More actions · Answer 7

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

UMG Developer SSChampion Points: 13482 More actions · Answer 8

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.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 9

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.

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 10

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 a lot word there), so I picked Answers B and C option. The following statement in this BOL page helped me to answer incorrectly:

The bulk-logged recovery model... provides the best performance and least log space usage.

On the same page though it also states that log backups under bulk-logged model can be

larger and take longer to complete than under the full recovery model

, 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

WayneS SSC Guru Points: 95460 More actions · Answer 11

wrt the log file... I think the give-away here was the wording it "will be a lot 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)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SanjayAttray SSChampion Points: 13157 More actions · Answer 12

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.

SQL DBA.

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 13

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.

KevinC. SSCommitted Points: 1570 More actions · Answer 14

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.

yogender SSC-Addicted Points: 477 More actions · Answer 15

yogender

SSC-Addicted

Points: 477

August 9, 2010 at 6:31 am

#1204107

good question.