• 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