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 ««1234»»»

An examination of bulk-logged recovery model Expand / Collapse
Author
Message
Posted Tuesday, May 8, 2012 5:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
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"
Post #1296367
Posted Tuesday, May 8, 2012 5:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 42,442, Visits: 35,496
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 2008, MVP
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

Post #1296375
Posted Tuesday, May 8, 2012 5:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 42,442, Visits: 35,496
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 2008, MVP
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

Post #1296376
Posted Tuesday, May 8, 2012 6:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 5:18 AM
Points: 28, Visits: 178
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!
Post #1296382
Posted Tuesday, May 8, 2012 6:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 42,442, Visits: 35,496
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 2008, MVP
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

Post #1296387
Posted Tuesday, May 8, 2012 6:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 5:18 AM
Points: 28, Visits: 178
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.


Understood. Mulling it over, this seems to make sense to me as a quick and dirty mental model for bulk logged operations:

We always need "before" and "after" images in user database to support recovery. For minimally logged operations in bulk-logged or simple recovery, the "after" image is only in the data file, hence the absolute need for Eager Write. The "before" image is assumed to consist of empty pages (allocation updates aside), hence the restriction on what can and cannot be minimally logged. So rollforward is based on the knowledge that the data is in the data file, and rollback is only possible because we know we are rolling back to empty pages.
Post #1296424
Posted Tuesday, May 8, 2012 1:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 30, 2014 7:44 AM
Points: 4, Visits: 478
great article!
Post #1296713
Posted Tuesday, May 8, 2012 8:00 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 4:40 PM
Points: 1,317, Visits: 509
Thanks Gail, great article and explanations. Clear and concise. Appreciate you clearing up that question for me.
Post #1296841
Posted Sunday, May 13, 2012 5:30 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Saturday, January 26, 2013 2:59 AM
Points: 199, Visits: 240
Hi Gail,

Thanks for the great article..

One question:
One side effect of this requirement that both log records and modified data pages are written to disk before the transaction commits is that it may actually result in the minimally logged operation being slower than a regular transaction if the data file is not able to handle the large volume of writes


I fail to fully understand why minimally logged operations can be slower . With the eager write process, should it not ease the burden on the transaction log by writing the dirty minimally logged records only to the data file? By splitting the writes as opposed to the fully logged model should it not be faster, especially if the log and data are on separate drives? Is flushing to the data files slower than writing to the log if both are sequential? I can understand it if the writes are random, but are minimal logged operations not all sequential in nature when writing to the data files?

Thanks for this..
Post #1299237
Posted Sunday, May 13, 2012 8:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:53 AM
Points: 4, Visits: 52
Hi Very nice Article.

Since i am a beginner, i had a question in my mind:

When we switch the database recovery mode, from Full recivery to bulk- logged or vice versa, does some changes happen to the existing log file of the database too??? (so as to align with the database recovery model).

Post #1299247
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse