Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Recovery Model and Transactions Expand / Collapse
Author
Message
Posted Thursday, May 7, 2009 2:39 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:35 AM
Points: 166, Visits: 1,056
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
Post #711835
Posted Thursday, May 7, 2009 5:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 2:13 PM
Points: 39,866, Visits: 36,208
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 2008, MVP
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

Post #711895
Posted Thursday, May 7, 2009 5:21 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:35 AM
Points: 166, Visits: 1,056
Thanks a lot Gila, that really helped!
Post #711904
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse