none
Transaction Log Size - Simple Recovery Model

    Question

  • I'm not too knowledgeable about SQL, but I read an article that says when the Simple Recovery Model is used, the transaction logs will not contain committed transactions, therefore they will never become very large.  Is this accurate?  Also, am I correct that I don't need to do transaction log backups in order to truncate the logs in this case (since they become truncated automatically)?  I'm referring to SQL versions 2005 and newer.

    I ask because I thought I read some conflicting information somewhere regarding this.

    In our scenario the Simple Recovery Model is appropriate, so I plan to use it.  I just want to make sure I will then not have to worry about transation log size.

    Thanks.

    Thursday, July 26, 2012 9:05 PM

Answers

  • Hi,

    Not only do you not need to do transaction log backups when using the Simple Recovery model, you can't! SQL Server won't let you - "Transaction Log" won't even appear as an option to backup when you're using Simple.

    With regards to the transaction log bloating, this shouldn't be an issue when you're using Simple. The transaction log is still used when you use Simple, but the transactions don't stay in there for very long, only until they're written to disk. 

    See here for a little more information on the Simple Recovery Model: http://www.mssqltips.com/sqlservertutorial/4/sql-server-simple-recovery-model/

    Hope that helps!


    Zach Stagers - scratchbox.co.uk
    MCITP: Database Developer 2008
    Remember to Mark as Answer and Vote as Helpful

    • Marked as answer by CFS3rd Friday, July 27, 2012 3:14 AM
    Thursday, July 26, 2012 9:48 PM
  • Zach,

    In your post you said:

    "With regards to the transaction log bloating, this shouldn't be an issue when you're using Simple. The transaction log is still used when you use Simple, but the transactions don't stay in there for very long, only until they're written to disk. "

    This is actually incorrect. Simple recovery model can still have transaction log issues, which you can replicate yourself. In the simple recover model the log is automatically truncated when certain triggers are hit, such as a checkpoint, % of log full, etc. It is entirely possible to create a very large long running transaction that causes the log to grow and grow and grow.....

    To test this out, create a database in simple recovery model and a very small log (10 MB). Create an open explicit transaction then run an infinite while loop to insert data into a table. You'll see the log grow and not truncate which can be check through the DBCC LOGINFO() undocumented command (though you can search your favorite search engine and get a good amount of information).

    However was said about transaction log backups is correct, you can't take them in simple mode as the log chain is broken when the log truncates.

    -Sean


    Sean Gallardy, MCC | Blog

    • Marked as answer by CFS3rd Friday, July 27, 2012 3:16 AM
    Friday, July 27, 2012 1:09 AM

All replies

  • Here is the article which seems to suggest truncation is needed even with the Simple Recovery Model:

    http://msdn.microsoft.com/en-us/library/ms178052%28v=sql.105%29.aspx

    "After switching (to Simple Recovery Model), you need to take periodic data backups to protect your data and to truncate the inactive portion of the transaction log."

    Thanks.

    Thursday, July 26, 2012 9:13 PM
  • Hi,

    Not only do you not need to do transaction log backups when using the Simple Recovery model, you can't! SQL Server won't let you - "Transaction Log" won't even appear as an option to backup when you're using Simple.

    With regards to the transaction log bloating, this shouldn't be an issue when you're using Simple. The transaction log is still used when you use Simple, but the transactions don't stay in there for very long, only until they're written to disk. 

    See here for a little more information on the Simple Recovery Model: http://www.mssqltips.com/sqlservertutorial/4/sql-server-simple-recovery-model/

    Hope that helps!


    Zach Stagers - scratchbox.co.uk
    MCITP: Database Developer 2008
    Remember to Mark as Answer and Vote as Helpful

    • Marked as answer by CFS3rd Friday, July 27, 2012 3:14 AM
    Thursday, July 26, 2012 9:48 PM
  • Zach,

    In your post you said:

    "With regards to the transaction log bloating, this shouldn't be an issue when you're using Simple. The transaction log is still used when you use Simple, but the transactions don't stay in there for very long, only until they're written to disk. "

    This is actually incorrect. Simple recovery model can still have transaction log issues, which you can replicate yourself. In the simple recover model the log is automatically truncated when certain triggers are hit, such as a checkpoint, % of log full, etc. It is entirely possible to create a very large long running transaction that causes the log to grow and grow and grow.....

    To test this out, create a database in simple recovery model and a very small log (10 MB). Create an open explicit transaction then run an infinite while loop to insert data into a table. You'll see the log grow and not truncate which can be check through the DBCC LOGINFO() undocumented command (though you can search your favorite search engine and get a good amount of information).

    However was said about transaction log backups is correct, you can't take them in simple mode as the log chain is broken when the log truncates.

    -Sean


    Sean Gallardy, MCC | Blog

    • Marked as answer by CFS3rd Friday, July 27, 2012 3:16 AM
    Friday, July 27, 2012 1:09 AM
  • Thanks Zach and Sean -

    Thanks for the explanations and the link to the mssqltips article, which is very well written.

    I had read about scenarios where something can cause the active transation logs to grow really large, so I realize that I will still have monitor log file growth.  I'll look into restricting file growth as a method of keeping this from getting out of control.  It's just kind of hard to determine what the limit should be.

    Friday, July 27, 2012 7:40 PM