Post reply

Simple recovery

  • December 19, 2005 at 8:58 am

    #96077

    Hi,

    The documentation states the following:

    The simple recovery model minimally logs most transactions, logging only the information required to ensure database consistency after a system crash or after restoring a data backup.

    I am a little unsure of what kind of database consistency would be required after a data backup, and what logging would need to be done to ensure such consistency. I would highly appreciate if someone clarified this. Thanks.

    Karim

  • December 19, 2005 at 9:11 am

    #610348

    A good example of this would be a large update statement that takes a while to complete.  If the server were to go down in the middle of the updates completing then the current records that have been updated would have been logged for a rollback.

  • December 19, 2005 at 9:15 am

    #610349

    Data consistency is needed within a transaction, which is what the log provides. A backup is a copy of the db at a point in time.

    So if you backed up at midnight, then you are consistent then. Remember that the backup takes time, say 2 minutes, so until that completes, you have no backup.

    You finish and now you have a load of data or some update statement at 8:00am that takes 12 seconds. You are updating 50 rows (I know it's long), and it runs in one transaction (as a single statement would). It completes 40 rows (changed in the tables) and your server crashes (power out, security reboot, something). Now on a restart, your db is inconsistent. You have 40 rows of 50 changed, and no report on the success or failure of your transaction.

    The log, however, will be in one of two states. Either the complete transaction was recorded in the log and marked as completed (writes there first and then writes the tables), or it was just recorded and not marked incomplete. In the first case, the transaction will "roll forward", in other words, complete. In the 2nd case, which seems to fit this scenario, the transaction will "roll back", and the 40 rows will be changed back.

    Now you don't know where in the transaction you are and the SQL error log will only report how many transactions rolled forward or back on restart, but once this happens, you are not "consistent"

Viewing 3 posts - 1 through 2 (of 2 total)

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