Bulk-Logged Mode

Question

Bulk-Logged Mode

Paul Randal

One Orange Chip

Points: 29544
More actions
June 7, 2010 at 10:22 pm

#219747

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

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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

UMG Developer SSChampion Points: 13482 More actions · Answer 2

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 3

Professional question & answer.

Good start into the day.

Best Regards,

Chris Büttner

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 4

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

again i learned something new today!

free_mascot One Orange Chip Points: 27169 More actions · Answer 5

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 6

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/

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 7

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

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 8

Good one.

Thanks.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

lbowman Default port Points: 1495 More actions · Answer 9

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.

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

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 11

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/

SQLAJ Ten Centuries Points: 1359 More actions · Answer 12

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

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

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

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 14

Excellent question and great explanation. Thanks!

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum