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 Monday, May 14, 2012 4:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:32 PM
Points: 2,270, Visits: 3,788
Great reference article.

Mohammed Moinudheen
Post #1299463
Posted Monday, May 14, 2012 11:00 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 @ 2:25 AM
Points: 42,802, Visits: 35,917
stevro (5/13/2012)

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?


With a fully logged operation only the log records have to be written to disk before the transaction is complete, the data pages can follow later.
If you've got a database where the data file is optimised for reading (say raid 5 and read caches) and a transaction log optimised for writing (say raid 10 and write caches), then it is possible that writing X GB of data to the data file will be slower than writing X GB of data to the log file would have been, and the transaction commit will have to wait for the write to the data to the data file to finish where normally it only has to wait for the write to the log.

I didn't say it will be slower, but it can be, maybe not usually, but it is a possibility.



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 #1299734
Posted Monday, May 14, 2012 12:30 PM


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 @ 2:25 AM
Points: 42,802, Visits: 35,917
deepak_19888 (5/13/2012)
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).


The change is logged, just like any other change to the database, but there's nothing that you would need to do other than setting the recovery model option.



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 #1299795
Posted Tuesday, May 15, 2012 2:32 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
If you've got a database where the data file is optimised for reading (say raid 5 and read caches) and a transaction log optimised for writing (say raid 10 and write caches), then it is possible that writing X GB of data to the data file will be slower than writing X GB of data to the log file would have been


Thanks Gail. This make sense.
Post #1300087
Posted Thursday, May 24, 2012 12:05 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 3:10 AM
Points: 880, Visits: 4,092
Truly briefcase item for me.
The complex topic in such a simple way( at least for me)

Thanks Gail

Should we live our life in bulk logged or full recovery mode?


----------
Ashish
Post #1306042
Posted Friday, May 25, 2012 5:11 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 @ 2:25 AM
Points: 42,802, Visits: 35,917
crazy4sql (5/24/2012)
Should we live our life in bulk logged or full recovery mode?


From the article:
Guidelines for Bulk-logged Recovery

The main guideline for bulk-logged recovery is: ‘as short a time as possible’.

Due to the implications for point-in-time restores and restoring to point of failure, if a database is switched to bulk-logged recovery in order to minimally log some operations, it should stay in bulk-logged recovery for the shortest time possible.



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 #1306453
Posted Monday, January 14, 2013 8:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 5:00 AM
Points: 36, Visits: 275
Thank you!

I was redoing everything you have done in this example but when I restore the log with CONTINUE_AFTER_ERROR I get all my data correctly. I have done this multiple times and I haven't got the message 'Attempt to fetch logical page in database failed'

I cannot understand why this is happening because when I did the log backup(tail) I already have deleted the mdf file , so there isn't any way SQL Server could get the data from.

Can you please help me?

Thank you
Post #1406783
Posted Monday, January 14, 2013 8:59 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 @ 2:25 AM
Points: 42,802, Visits: 35,917
Without all your code, I can't say. Maybe the operations weren't minimally logged (existing table, full recovery or any of the other reasons for not minimally logging)


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 #1406786
Posted Monday, January 14, 2013 11:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 5:00 AM
Points: 36, Visits: 275
Thank you for your reply. As soon as i get to my office I will get all the code I have used and i will post here.
GilaMonster (1/14/2013)
Without all your code, I can't say. Maybe the operations weren't minimally logged (existing table, full recovery or any of the other reasons for not minimally logging)
Post #1406868
Posted Monday, January 14, 2013 12:23 PM


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 @ 2:25 AM
Points: 42,802, Visits: 35,917
Just go over it a couple times, make sure you are getting minimal logging. I ran that particular test a number of times, as did a friend and, while we got a variety of different errors, we did not get the table back intact after any of the tests.


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 #1406902
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse