Recovery Models

  • Excellent article and great discussion about it here! Thank you.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • Enjoyed the article.

    Thinking over it I came to this question:

    You write: "An operation is said to be minimally-logged if all it logs are the allocation changes."

    In the case of a bulk insert this would mean either that a bulk insert can use only fresh allocation units (pages?) or that your statement must be rewritten to "logs which allocation units have been affected"

    And a second question: what exactly cannot be recovered in case of minimal logging?

  • h.tobisch (9/2/2011)


    You write: "An operation is said to be minimally-logged if all it logs are the allocation changes."

    In the case of a bulk insert this would mean either that a bulk insert can use only fresh allocation units (pages?) or that your statement must be rewritten to "logs which allocation units have been affected"

    From Books Online:

    Whether minimal logging can occur for a table also depends on whether the table is indexed and, if so, whether the table is empty:

    If the table has no indexes, data pages are minimally logged.

    If the table has no clustered index but has one or more nonclustered indexes, data pages are always minimally logged. How index pages are logged, however, depends on whether the table is empty:

    * If the table is empty, index pages are minimally logged.

    * If table is non-empty, index pages are fully logged.

    If the table has a clustered index and is empty, both data and index pages are minimally logged. In contrast, if a table has a clustered index and is non-empty, data pages and index pages are both fully logged regardless of the recovery model.

    And a second question: what exactly cannot be recovered in case of minimal logging?

    Don't understand the question. SQL ensures, no matter what, a database can be recovered in case of a shutdown (if it can't it goes suspect)

    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
  • h.tobisch (9/2/2011)


    Enjoyed the article.

    And a second question: what exactly cannot be recovered in case of minimal logging?

    AFAIK, the data that was minimally logged during the current log interval. Like with the example of the 50 GB bulk import. If you switch to bulk log, did the import, switched back to full and then there was a disaster immediately after and you hadn't backed up your log again, that minimally logged data in that log interval may be lost. Is that correct Gail?

    If a disaster occurs to a database in bulk-logged recovery, even if the log file is intact and undamaged, it may not be possible to take a tail-log backup. To take a tail-log backup of a database in bulk-logged recovery where the data file is missing or damaged, there must have been no bulk-operations since the last log backup. Similarly, to restore a database in bulk-logged recovery to a point in time, that time must be within a log interval (time covered by a single log backup) in which no minimally logged operations occurred. If any minimally logged operations occurred within a log interval, the database can be restored only to the beginning or end of that log interval, not to a point in the middle.

    Because of these limitations, having a database in bulk-logged recovery increase the chances of data loss in the case of a disaster. Hence it is more common to switch databases temporarily to bulk-logged recovery for certain operations (like index rebuilds) and then back to full recovery afterwards, than to have them in bulk-logged recovery permanently.

    ....

    That said it's a very good idea to take a log backup after switching back to full recovery so that the current log interval does not contain any minimally-logged operations that could prevent tail-log backups or point-in-time recovery.

  • Depends on what kind of disaster, what it damaged and what it left intact.

    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 (9/2/2011)


    Depends on what kind of disaster, what it damaged and what it left intact.

    Would it make a difference if you were able to take a tail log backup of the database in the specified scenario?

  • Excellent article!

    It's perfect for refreshing my memory on this very important subject.

  • I am afraid I was not able to convey the problem I see behind my question: if . as stated by Gail, minimal logging records allcation changes by a minimally logged statement, it would not be possible to reconstruct data this statement puts into already existing allocation units. So I suppose it should read "allocation units affected by the statement".

  • h.tobisch (9/6/2011)


    I am afraid I was not able to convey the problem I see behind my question: if . as stated by Gail, minimal logging records allcation changes by a minimally logged statement, it would not be possible to reconstruct data this statement puts into already existing allocation units. So I suppose it should read "allocation units affected by the statement".

    But if you read the piece I quoted from BoL, Minimal logging requires that the table is empty or a heap. So only new extents (not allocation units) are affected by a minimally logged insert.

    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
  • Really nice article Gail, to understand the recovery models. Enjoyed it thoroughly 🙂


    Sujeet Singh

  • Indeed a nice article, Gail

    Another one to be ref-ed in the fora fairly frequent 😀

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 11 posts - 16 through 26 (of 26 total)

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