An examination of bulk-logged recovery model

Question

Post reply

An examination of bulk-logged recovery model

Gail Shaw

SSC Guru

Points: 1004504
More actions
May 5, 2012 at 8:36 pm

#268384

Comments posted to this topic are about the item An examination of bulk-logged recovery model
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

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

Skandalis Hall of Fame Points: 3517 More actions · Answer 1

Skandalis

Hall of Fame

Points: 3517

May 7, 2012 at 3:12 am

#1484007

Thanks Gail.

Well worth the read!

Chris Houghton SSCrazy Points: 2557 More actions · Answer 2

Thank you Gail, your article has really helped further my understanding of this rather murky area. It's great that you and other experts take the time out to help the community, it really is appreciated.

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 3

Awesome!

Thank you, Gail, for taking the time to write such a thorough, detailed article.

Chintak Chhapia Right there with Babe Points: 748 More actions · Answer 4

Chintak Chhapia

Right there with Babe

Points: 748

May 7, 2012 at 2:02 pm

#1484217

Superb Article...

Kind Regards,

Chintak Chhapia

matt.bowler SSCrazy Points: 2667 More actions · Answer 5

Thank you Gail for the excellent article.

I do have one question: in the final demo where you looked at the log usage for an index rebuild, you mentioned "log reservation in case of rollback". Could you clarify what you mean by this?

iugdds SSC Enthusiast Points: 173 More actions · Answer 6

==== ( http://www.fullmalls.com ) =====

$33 True Religion jeans, Ed Hardy jeans,LV,Coogi jeans,Affliction jeans

$30 Air Jordan shoes,Shox shoes,Gucci,LV shoes

50%Discount winter fashion :Sandle,t-shirt,caps,jerseys,handbag and brand watches!!!

$15 Ed Hardy ,LV ,Gucci Bikini

$15 Polo, Ed Hardy, Gucci, LV, Lacoste T-shirts

$25 Coach,Gucci,LV,Prada,Juicy,Chanel handbag,

$10 Gucci,Ed Hardy sunglasses

$9 New Era caps.

give you the unexpected harvest

==== ( http://www.scnshop.com ) =====

==== ( http://www.fullmalls.com ) =====

iugdds SSC Enthusiast Points: 173 More actions · Answer 7

==== ( http://www.fullmalls.com ) =====

$33 True Religion jeans, Ed Hardy jeans,LV,Coogi jeans,Affliction jeans

$30 Air Jordan shoes,Shox shoes,Gucci,LV shoes

50%Discount winter fashion :Sandle,t-shirt,caps,jerseys,handbag and brand watches!!!

$15 Ed Hardy ,LV ,Gucci Bikini

$15 Polo, Ed Hardy, Gucci, LV, Lacoste T-shirts

$25 Coach,Gucci,LV,Prada,Juicy,Chanel handbag,

$10 Gucci,Ed Hardy sunglasses

$9 New Era caps.

give you the unexpected harvest

==== ( http://www.scnshop.com ) =====

==== ( http://www.fullmalls.com ) =====

iugdds SSC Enthusiast Points: 173 More actions · Answer 8

==== ( http://www.fullmalls.com ) =====

$33 True Religion jeans, Ed Hardy jeans,LV,Coogi jeans,Affliction jeans

$30 Air Jordan shoes,Shox shoes,Gucci,LV shoes

50%Discount winter fashion :Sandle,t-shirt,caps,jerseys,handbag and brand watches!!!

$15 Ed Hardy ,LV ,Gucci Bikini

$15 Polo, Ed Hardy, Gucci, LV, Lacoste T-shirts

$25 Coach,Gucci,LV,Prada,Juicy,Chanel handbag,

$10 Gucci,Ed Hardy sunglasses

$9 New Era caps.

give you the unexpected harvest

==== ( http://www.scnshop.com ) =====

==== ( http://www.fullmalls.com ) =====

iugdds SSC Enthusiast Points: 173 More actions · Answer 9

==== ( http://www.fullmalls.com ) =====

$33 True Religion jeans, Ed Hardy jeans,LV,Coogi jeans,Affliction jeans

$30 Air Jordan shoes,Shox shoes,Gucci,LV shoes

50%Discount winter fashion :Sandle,t-shirt,caps,jerseys,handbag and brand watches!!!

$15 Ed Hardy ,LV ,Gucci Bikini

$15 Polo, Ed Hardy, Gucci, LV, Lacoste T-shirts

$25 Coach,Gucci,LV,Prada,Juicy,Chanel handbag,

$10 Gucci,Ed Hardy sunglasses

$9 New Era caps.

give you the unexpected harvest

==== ( http://www.scnshop.com ) =====

==== ( http://www.fullmalls.com ) =====

ChiragNS One Orange Chip Points: 26137 More actions · Answer 10

Great article Gail.

One doubt though - you mentioned that if we do a log backup when a minimally logged operation is running the log backup contains the "image" of pages being modified by the minimally logged operation.

Will this increase the log file size or only the log backup size?

Thanks

"Keep Trying"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

matt.bowler (5/7/2012)
I do have one question: in the final demo where you looked at the log usage for an index rebuild, you mentioned "log reservation in case of rollback". Could you clarify what you mean by this?

Sure.

High level summary: If an operation is rolled back, SQL performs the undo by reading the transaction log and generating compensating operations to reverse the effects of whatever is being rolled back. Those operations, like any other data modification, get logged.

A rollback cannot be allowed to fail if say the rollback is caused by a full transaction log, that would result in the database being marked suspect, which is a very bad thing.

Hence, while SQL is performing any data modification and logging the changes, it also reserves enough space in the log to be able to log the undo of those operations. This is log reservation. The estimation that SQL uses is a very conservative one, it's better to reserve more log space than is actually needed than to not reserve enough and send the DB suspect.

http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-25-Why-isnt-my-log-backup-the-same-size-as-my-log.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

ChiragNS (5/8/2012)
Will this increase the log file size or only the log backup size?

Just the log backup. The pages don't go anywhere near the log file itself, they're just copied into the log backup when it is taken.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ewald Cress SSC Veteran Points: 212 More actions · Answer 13

Thanks Gail, great article.

I thought I had a decent grasp of logging issues, but I must admit that I had a light-bulb moment when you described the Eager Write mechanism!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Ewald Cress (5/8/2012)
I thought I had a decent grasp of logging issues, but I must admit that I had a light-bulb moment when you described the Eager Write mechanism!

Please note that Eager Writes are just for minimally logged operations in bulk-logged or simple recovery, and they just write the minimally logged pages to the data file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass