Post reply

Recovery Model and Transactions

  • May 7, 2009 at 2:39 am

    #204432

    Hi

    In SQL2008's BOL, it says that "[r]ecovery models are designed to control transaction log maintenance." About the simple recovery model it says that "[t]he simple recovery model minimizes administrative overhead for the transaction log, because the transaction log is not backed up. The simple recovery model risks significant work-loss exposure if the database is damaged. Data is recoverable only to the most recent backup of the lost data."

    Please note that these questions exclude the data-loss aspect of this issue.

    Does the choice of recovery model affect transaction handling in the database? Not the backup and recovery of the transaction log, but the actual transactions themselves. Can I still use transactions? Does the transaction log still exist? Will the transaction log increase in size over time or is it cleared periodically, and if so, when?

    Thanks

  • May 7, 2009 at 5:07 am

    #990230

    GDI Lord (5/7/2009)

    Does the choice of recovery model affect transaction handling in the database?

    No, other than for bulk operations (detailed below).

    Can I still use transactions?

    Yes

    Does the transaction log still exist?

    Yes. And all transactions will be logged. Logging is not optional and there's no way to stop an operation from logging. The only difference is that some operations are fully logged in full recovery and minimally logged in Simple and Bulk Logged recoveries. See Books Online for more details, look up "minimally logged" or "Bulk logging"

    Will the transaction log increase in size over time or is it cleared periodically, and if so, when?

    Maybe and yes.

    In simple recovery, the log is truncated when a checkpoint operation runs (note, truncated, not shrink). When the log is truncated, inactive log records (those relating to transactions whose changes are hardened on disk) are removed from the log, and the space made available for reuse. However a truncation cannot clear log records where there's log entry that pertains to an open transaction, or to an unreplicated transaction transactional replication), so either of those could prevent truncation and result in the log growing.

    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

  • May 7, 2009 at 5:21 am

    #990237

    Thanks a lot Gila, that really helped!

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

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