What Happens to Your Transaction Log in SIMPLE Recovery Model?

PASS Summit Unite 2009 Speaker Tip

SIMPLE recovery model causes SQL Server to truncate the transaction log every time a CHECKPOINT operation occurs.

Before SQL Server 2008, you could force a truncation with an option to the BACKUP LOG command:

BACKUP LOG  WITH TRUNCATE_ONLY

Starting in SQL Server 2008, however, this option is no longer available. If you want to truncate the log, the only way to do it is to switch your database to SIMPLE recovery. But because the log is regularly being truncated, you cannot take backups of the transaction log. In order to make log backups, you need to start with a full database backup and then keep an unbroken chain of log backups, so a truncation of the log will disallow log backups.

In addition, if you have never performed a full database backup, you can’t make log backups, so the database will behave as if you are in SIMPLE recovery. You will be in a mode called auto_truncate, and even though the recovery model might be FULL, your log will be truncated every time a CHECKPOINT occurs.

Read more from Kalen about her Nov. 2 PASS Summit 2009 pre-conference seminar, "Care and Feeding of the Transaction Log."


Editor's Note: SQL Server Magazine would like to thank PASS for providing this technical tip.