Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Bulk-Logged Mode Expand / Collapse
Author
Message
Posted Tuesday, June 8, 2010 7:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 3, 2014 11:47 AM
Points: 2,038, Visits: 1,664
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
Post #934007
Posted Tuesday, June 8, 2010 8:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:28 PM
Points: 5,969, Visits: 8,224
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #934015
Posted Tuesday, June 8, 2010 8:05 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 5:45 AM
Points: 85, Visits: 399
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
Post #934021
Posted Tuesday, June 8, 2010 8:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 3, 2014 11:47 AM
Points: 2,038, Visits: 1,664
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
Post #934026
Posted Tuesday, June 8, 2010 9:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 1, 2014 9:46 PM
Points: 1,380, Visits: 2,684
Excellent question and great explanation. Thanks!

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Post #934062
Posted Tuesday, June 8, 2010 10:23 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 4:02 PM
Points: 1,521, Visits: 124
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
Post #934104
Posted Tuesday, June 8, 2010 10:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 18, 2014 8:44 AM
Points: 166, Visits: 199
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?





Post #934133
Posted Tuesday, June 8, 2010 11:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 18, 2014 8:44 AM
Points: 166, Visits: 199


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



+1



Post #934138
Posted Tuesday, June 8, 2010 11:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 3, 2014 11:47 AM
Points: 2,038, Visits: 1,664
@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
Post #934152
Posted Tuesday, June 8, 2010 11:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 3, 2014 11:47 AM
Points: 2,038, Visits: 1,664
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
Post #934160
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse