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
jamesmetcalf74Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.