Auto-Truncate Log in Full Recovery Model

FULL Recovery model: This means that all database changes are fully logged and ideally the log records should stay in the log file until the log records are safely stored away\backed up in a Transaction Log backup. As per MSDN:  If a DB is in Full Recovery Model, then No work is lost due to a lost or damaged data file & we Can recover to an arbitrary point in time (for example, prior to application or user error) provided we have all backups in place.

But unfortunately this is not completely correct. Your database can be in Auto-Truncate Mode while the recovery model is set to FULL.

Now what is Auto-Truncate Mode: This means your DB is still working as if it is in SIMPLE Recovery Model. or in other words, the log file will be truncated every time a CHECKPOINT is run against the database. CHECKPOINT happens at regular intervals. Log Truncation means that inactive parts of the log file will be overwritten if the log space is needed.  Log Truncation does not physically shrink the log file, but it can keep it from physically growing.

In this article i will explain you the different conditions which changes this behaviour & let you know how to avoid such pitfalls and make your database fully recoverable. This all is internally related to LSN.

Now What is LSN?

Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken. Once the LSN Chain (Log Chain) is broken, you can never do point in time recovery. (there are few exceptions to this, i'll explain this later)

There are four conditions in which Auto-Truncate Mode gets enabled:

1. By switching the recovery model of your database to simple.
2. Using BACKUP LOG command with NO_LOG | TRUNCATE_ONLY option. (Note:  The BACKUP LOG ... WITH NO_LOG | TRUNCATE_ONLY option is no longer available in SQL Server 2008)
3. If you have never taken a FULL backup of your database, log backup of that database will also be not available. Your database is in   Auto-Truncate Mode until the first FULL backup of the database is taken. 
4. If you have not taken a FULL\Differential backup after the last time you switched from SIMPLE to either FULL or BULK_LOGGED recovery model.

You should also be aware that taking a log backup will truncate the log, but taking a FULL database backup will not truncate the log. (I received many queries from DBA’s asking this specific question)

How to check whether your database is in Auto-Truncate Mode or not?

You can query a system view called sys.database_recovery_status. If the value of last_log_backup_lsn column of this view is NULL it means the database is not maintaining a sequence of log backups and it is in Auto-Truncate Mode:

Note: To see the row for a database other than master or tempdb, you should have one of the following permission:

• You should be owner of the database.
• You should have ALTER ANY DATABASE or VIEW ANY DATABASE server-level permissions.
• You should have CREATE DATABASE permission in the master database.

SELECT @@servername,db_name(database_id) as 'database', last_log_backup_lsn FROM sys.database_recovery_status(nolock)

.

.

How to resolve this?

To resolve this you need to either take a FUll or Differential backups to bridge a gap & recreate a new LSN Chain.

To replicate the same and test following is the step by step script.

.

CREATE DATABASE sarab_12

GO

-- Check the status of auto-truncate option after first creating Database

SELECT db_name(database_id) as 'database', last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = db_id('sarab_12')

GO

--Now take a full backup of sarab_12 Database

BACKUP DATABASE sarab_12 TO disk = 'C:\sarab_12.bak'

GO

-- Check status of auto-truncate option after taking full backing up of sarab_12 Database

SELECT db_name(database_id) AS 'database', last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = db_id('sarab_12')

GO

--Try to truncate the inactive portion of sarab_12 database without taking backup of the same

BACKUP LOG sarab_12 WITH TRUNCATE_ONLY

GO

-- Check status of auto-truncate option after truncating log

SELECT db_name(database_id) AS 'database', last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = db_id('sarab_12')

GO

--Clean your instance

DROP DATABASE sarab_12

GO

APPLIES TO
MS SQL Server 2000

MS SQL Server 2005
MS SQL Server 2008

Regards,

Sarabpreet Singh