Recovery Models

  • 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
  • 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?

  • 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

  • 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%

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

  • 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
  • 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
    Consultant - Straight Path Solutions
    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

  • 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 

  • One undo of a truncate coming right up...

    CREATE TABLE TestingTruncate (SomeUselessColumn varchar(50))

    INSERT INTO TestingTruncate

    SELECT NAME FROM sys.columns

    SELECT * FROM TestingTruncate -- 501 rows

    BEGIN TRANSACTION

    TRUNCATE TABLE TestingTruncate

    SELECT * FROM TestingTruncate -- 0 rows

    ROLLBACK TRANSACTION

    SELECT * FROM TestingTruncate -- 501 rows

    btw, Create and Drop table are fully undoable as well.

    BEGIN TRANSACTION

    DROP TABLE TestingTruncate

    ROLLBACK TRANSACTION

    SELECT * FROM TestingTruncate

    A similar example with create table is left as an exercise to the reader.

    If an operation didn't write log entries for undo, then if that operation was part of a transaction and the transaction failed, the entire database would have to be marked suspect.

    If an operation didn't write log entries for redo then neither mirroring nor log shipping (nor transactional replication) would be able to repeat that operation at the mirror/secondary

    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
  • Yet another valuable lesson learned.

    Regards, Irish 

  • Good article.

    I know people like lists of myths, but I remember reading that the way our memories work is often context-free, and sometimes these lists reinforce the myths in people's minds if they don't spend much time trying to integrate the detailed debunking into what they know.

    I'm not sure what to do with that, other than maybe to try to present the negation of the myth in emphasized form, rather than the myths themselves.

  • Thanks for the nice article I will bookmark it for my favourite.

    One question about the below statement,

    Because of this behaviour, when a database is in full recovery model and the database is damaged in such a way that the transaction log is intact and undamaged, a tail-log backup can be taken which holds the last transactions that occurred in that database. Hence, assuming the database's transaction log is intact, it is possible to restore without data loss in case of a disaster.

    How do we know if it is the data file or transaction log file is damaged?

    How can we check if a transaction log is intact or not?

    Thanks

  • In the context of the quote it's usually pretty clear. If there's been some disaster you will know (or easily be able to find out) whether the drive that the log is on is intact or not.

    Other than that, does a log backup succeed? If yes, the log file's intact and undamaged.

    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 26 total)

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