This title is almost a misnomer because, to a large degree, working in SIMPLE mode negates the need to manage the transaction log. In SIMPLE mode, the sole purpose of the transaction log is to ensure the ACID properties of transactions and to enforce database consistency and transaction durability, during database recovery operations. The transaction log cannot be backed up and used for database restores, nor for log shipping.

Working in SIMPLE mode

All transactions are still logged, although certain bulk operations are minimally logged; in effect, the level of logging is very similar to that applied in BULK_LOGGED mode. The active part of the log is maintained as normal so that whenever a database in SIMPLE mode restarts, the recovery process will kick in and the data files will be reconciled with the contents of the transaction log.

However, in SIMPLE mode, all virtual log files (VLF) marked as inactive (recoverable), as described in Level 2, are automatically truncated during regular database checkpoints. This means that any VLF where the highest LSN in that file is lower than the MinLSN will be truncated when a checkpoint takes place. As a result, space in the transaction log is regularly and routinely reused.

A database in SIMPLE recovery mode is always in auto-truncate mode. As noted in Level 3, all user databases will, in effect, be in auto-truncate mode until the first full database backup is performed.

How often do checkpoints happen?

The SQL Server engine decides how often to perform a checkpoint based on how many log records it will need to process in order to recover a database in the time specified by the recovery interval server configuration option. If your database is mainly read-only, the time between checkpoints may be long. However, on busy, frequently-updated systems, checkpoints can occur about every minute. See http://msdn.microsoft.com/en-gb/library/ms189573.aspx for more details.

As discussed in previous Levels, in FULL recovery mode the transaction log maintains a "history of inactive/closed transactions", along with the active/open ones. This "history" can be captured in a log backup and used to restore the database to a previous point in time. However, in SIMPLE mode, this history does not exist and so the log cannot be used to restore the database to a previous point in time. In fact, in SIMPLE recovery mode, you cannot even perform transaction log backups, as Listing 4.1 demonstrates.

  USE master;
  ALTER DATABASE TestDB
  SET RECOVERY SIMPLE;
  BACKUP Log TestDB
    TO DISK ='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\BACKUP\TestDB_log.bak'
    GO
  Msg 4208, Level 16, State 1,  Line 1
    The statement BACKUP LOG is not  allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the  recovery model using ALTER DATABASE.
    Msg 3013, Level 16, State 1,  Line 1
    BACKUP LOG is terminating  abnormally.

Listing 4.1: You can't perform log backups in SIMPLE mode

This means that your backup scheme will consist entirely of full (and possibly differential) database backups.

Pro and Cons of SIMPLE Mode

The downside to working in SIMPLE mode, of course, is that your exposure to the risk of data loss might be quite high, as you can only restore a database to the time of the most recent full or differential backup. As noted previously, if you want your exposure to data loss to be measured in minutes rather than hours, don't use SIMPLE mode.

However, if you are running a development or test database, or perhaps even a production database that is mainly read-only, then use of SIMPLE mode may well be a viable and even sensible option, and will greatly ease the maintenance burdens on that database. Less backup storage space will be required, and subsequent restore operations will be much simpler. Furthermore, since the transaction log is auto-truncated, you are much less exposed to the risk of it growing out of control, and potentially causing 9002 errors.

Although, SIMPLE mode significantly eases the burden of transaction log management, it's a mistake to assume that, if you're using this mode, you can completely forget about managing the log. The transaction log is still playing a vital role in the day-to-day operation of the database, and you still need to size and grow the transaction log appropriately, according to the nature and frequency of transactions to which the database is subjected. Just because the log is auto-truncated, it does not mean that hefty and long running transactions cannot cause the log to expand rapidly, and cause you trouble if you haven't sized it correctly – more on this in Levels 7 and 8.