SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recovery Models


Recovery Models

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)

Group: General Forum Members
Points: 371227 Visits: 46974
Comments posted to this topic are about the item Recovery Models

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


paul.knibbs
paul.knibbs
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5836 Visits: 6240
Nice article, but it just reinforces a query I've had for a while: why does bulk-logged recovery mode even exist? It has some hefty downsides compared to full recovery, and the only advantage I can see is that your log file might not get quite so big when using it, which seems a fairly minimal sort of advantage to my mind. Does someone with more SQL experience than me (i.e. practically everybody) have an example of a situation where it's really better to use bulk-logged recovery mode?
M&M
M&M
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8912 Visits: 3914
Excellent article Gail. I liked all the myths and misconceptions you mentioned escpecially this one.

Myth 4: You have to switch from simple recovery to bulk-logged recovery model to get minimally logged operations.

M&M
Dave Poole
Dave Poole
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24309 Visits: 3494
Gail,

I believe that it isn't simply a case of truncating on checkpoint for simple mode. I could be wrong but I thought it was a case of the truncation occuring for checkpointed transactions when the transaction log reaches a certain percentage of fullness i.e. 70%

LinkedIn Profile
www.simple-talk.com
GilaMonster
GilaMonster
SSC Guru
SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)

Group: General Forum Members
Points: 371227 Visits: 46974
David.Poole (9/1/2011)
I believe that it isn't simply a case of truncating on checkpoint for simple mode. I could be wrong but I thought it was a case of the truncation occuring for checkpointed transactions when the transaction log reaches a certain percentage of fullness i.e. 70%


Checkpoint truncates the log every time it runs. You can prove that by running various data-modification queries, running checkpoint and then checking fn_dblog (I do that any time I want to examine what an operation logs)

That 70% (iirc) is specifically for TempDB. It has some very different rules

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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)

Group: General Forum Members
Points: 371227 Visits: 46974
paul.knibbs (9/1/2011)
Nice article, but it just reinforces a query I've had for a while: why does bulk-logged recovery mode even exist? It has some hefty downsides compared to full recovery, and the only advantage I can see is that your log file might not get quite so big when using it, which seems a fairly minimal sort of advantage to my mind. Does someone with more SQL experience than me (i.e. practically everybody) have an example of a situation where it's really better to use bulk-logged recovery mode?


When you want to do major data loads, you don't want the overhead of full logging (nor the log growth) but you don't want to switch to simple recovery.

Let's say someone's bulk-inserting 50GB of data (maybe a bit extreme). In full recovery, that's at least 50GB of log space used. In bulk-logged, it's a lot less.

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


paul.knibbs
paul.knibbs
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5836 Visits: 6240
True, but since you need to switch it back to full recovery and then take a log backup--which, as the article said, will easily be the full 50Gb and change thanks to having to include all the altered pages--I'm still not sure this offers a major advantage! You're just moving the 50Gb of disk space usage from one place to another.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)

Group: General Forum Members
Points: 371227 Visits: 46974
paul.knibbs (9/1/2011)
I'm still not sure this offers a major advantage! You're just moving the 50Gb of disk space usage from one place to another.


The log backup if you'd done the load in full recovery would also be around 50GB or more (the entire used log space would get backed up).

It's also a time advantage not just a space one. Less logging = less overhead = faster load.

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


Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71029 Visits: 14951
Good article with simple concise explanations. I actually don't remember knowing that the Simple recovery model had minimally logged operations. I thought the logging was the same as FULL. Learned something new even though I thought I understood the topic.

One note, I usually change the model database to simple because I have the model log grow and I don't want or need to do log backups. This does mean I need to specifically set the recovery model to FULL for new databases that I know need log backups, but I normally do that anyway.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Jeffrey Irish
Jeffrey Irish
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1336 Visits: 1144
Gail,

I always enjoy reading your posts and blogs. It is awesome that you are so willing to share your knowledge and experience.

One of the items you noted is
Truncate table, for example, only logs the page de-allocations (as does drop table), but since that is enough information to fully redo the truncate table, that is classified as ‘fully logged’

Now I know that Truncate Table, like Create Table or Alter Table, are Data Definition Language (DDL) and I always thought that they were stand-alone operations and could not be redone or undone.

Now I know at the very least they are logged for "Redo" operations, but am I correct in thinking that there is still no "undo" short of completing a point-in-time recovery before the DDL operation was run?

Regards,

Irish w00t
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search