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. But unfortunately this is not completely correct. Your database can be in auto-truncate mode while the recovery model is set to FULL.

This document will help you know how to avoid such pitfalls and make your database fully recoverable.

When the database is in auto-truncate mode, 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.

There are four ways to enable auto-truncate mode:

1. By switching the recovery model of your database into 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 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 calls from DBA’s asking this specific question)

Now how to check whether your database is in auto-truncate mode or not?

You can query an undocumented 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)

.

.

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