Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Stairway to Transaction Log Management in SQL Server, Level 4: Managing the Log in Simple Recovery Mode

By Tony Davis,

The Series

This article is part of the Stairway Series: Stairway to Transaction Log Management in SQL Server

When things are going well, there is no need to be particularly conscious of what the Transaction log does or how it works. You just need to be confident that every database has the correct backup regime in place. When things go wrong, an understanding of the transaction log is important for taking corrective action, particularly when a point-in-time restore of a database is required, urgently! Tony Davis gives just the right level of detail that every DBA should know.

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.

Resources:

TLogStairway_Level4.sql

This article is part of the Stairway to Transaction Log Management in SQL Server Stairway

Sign up to our RSS feed and get notified as soon as we publish a new level in the Stairway! Rss

Total article views: 6391 | Views in the last 30 days: 33
 
Related Articles
FORUM

Can we take a transaction log backup in simple recovery model?

Can we take a transaction log backup in simple recovery model?

ARTICLE

Speedy Database Recovery with Differential Backups

Differential backups are a new feature in SQL 7 that can speed up your recovery times when restoring...

FORUM

Simple Backup for Small Database

What's the basics about how to backup a simple database?

FORUM

Simple recovery model

Simple recovery model

FORUM

simple recovery model Log truncation

simple recovery model Log truncation

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones