Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 99
  • Last Modified:

simple versus full recovery model sql

I was having a problem with transaction log files growing too large until I got the transaction log files
backing up with a database in the full recovery model.

Question....
if a database is set to a simple recovery model... will the transaction logs automatically be truncated without them being backed up?

how does a simple recovery model utilize the transaction logs... are they used at all?

also, shouldn't system databases be set to a simple recovery model in most cases?  I have some with the full recovery model selected
0
jamesmetcalf74
Asked:
jamesmetcalf74
1 Solution
 
Scott PletcherSenior DBACommented:
>> if a database is set to a simple recovery model... will the transaction logs automatically be truncated without them being backed up? <<

Yes.  Logs will be truncated at every checkpoint (every few mins).  But not shrunk: you must explicitly shrink files in SQL Server, it never does that automatically (unless you set autoshrink on, which you should never do).


>> how does a simple recovery model utilize the transaction logs... are they used at all? <<

Yes.  SQL always requires a log file to provide the ACID properties of a transaction, that is, to ensure "all or nothing" transactions, which includes rollback capabilities.


>> also, shouldn't system databases be set to a simple recovery model in most cases? <<

Yes.  Master, model and tempdb should always be simple.  Msdb can be simple or full, whichever you prefer.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now