SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recovery Model and Transactions


Recovery Model and Transactions

Author
Message
Andrew Jackson
Andrew Jackson
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
Points: 421 Visits: 1121
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87583 Visits: 45272
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


Andrew Jackson
Andrew Jackson
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
Points: 421 Visits: 1121
Thanks a lot Gila, that really helped!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search