SQL Server : Transaction Logs

Recovery Models

The following recovery models are available.

• Simple : No log backups. No point in time recovery.
• Full : Must do log backups or transaction logs will grow continually until the next backup.
• Bulk Logged : Bulk operations will not be logged completely. Non-bulk operations will be logged.
  Must do log backups, but may not be possible to do PITR if bulk operations were performed.

Managing Transaction Logs

Logs are written to sequentially, so there is no performance gain with striping.

• In simple mode, as soon as a transaction is complete, the log space is marked as reusable.
• In full mode, after the first full backup the log space will be kept until the next log backup. Before the first full backup, the database acts like pseudo-simple mode. It doesn’t act like true full mode until after the first full backup.

Monitor the log usage as follows.

SELECT name, recovery_model_dec, log_reuse_wait_desc
FROM sys.databases

dbcc sqlperf(logspace)

For more information see:

• Recovery Models (SQL Server)