It’s Simple!! Or Pseudo Simple Anyway

MSDN documents the log chain as:

A continuous sequence of log backups is called a log chain. A log chain starts with a full backup of the database. Usually, a new log chain is only started when the database is backed up for the first time
or after the recovery model is switched from simple recovery to full or bulk-logged recovery.

This is accurate enough except for the time between changing the recovery model to full or bulk logged and when a database is backed up. After the database recovery model is changed, the transaction log will continue to be truncated upon checkpoint. The reason is that the database engine is aware that there is no way to recover the database to a point in time without a backup. This recovery state is sometimes referred to as “Pseudo-Simple” recovery model.

This state can be easily demonstrated. The below T-SQL code creates a database, pseudoSimple, and sets its recovery model to full. The sys.databases catalog view is queried to show the recovery model and the log_reuse_wait_desc.

USE MASTER;
GO

IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘pseudoSimple’)
BEGIN
DROP DATABASE pseudoSimple
END

CREATE DATABASE pseudoSimple;
GO

ALTER DATABASE pseudoSimple SET RECOVERY FULL;
GO

SELECT name,
recovery_model,
log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘pseudoSimple’;
GO

The result is:

Now we can simulate activity that would cause the transaction log to record transactions and then requery the catalog view to see the log_reuse_wait_desc.

USE pseudoSimple;
GO

IF EXISTS(SELECT * FROM sys.tables WHERE name = ‘test’)
BEGIN
DROP TABLE test
END

CREATE TABLE test(
col1   CHAR(150)
);
GO

DECLARE @i INT = 1000
WHILE @i > 0
BEGIN

INSERT test
SELECT ‘This is number ‘ + CONVERT(CHAR(5), @i)

SET @i -= 1 END

–WAIT to insure transaction completes
WAITFOR DELAY ‘0:00:05′;
GO

SELECT name,
recovery_model,
log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘pseudoSimple’;
GO

Again, the results are the same:

So now let’s backup the database, add some records, and once again view the log_reuse_wait_desc:

–BACKUP the database
BACKUP DATABASE pseudoSimple
TO DISK = ‘C:\Backup\pseudo.bak’;
GO

IF EXISTS(SELECT * FROM sys.tables WHERE name = ‘test’)
BEGIN
DROP TABLE test
END

CREATE TABLE test(
col1   CHAR(150)
);
GO

DECLARE @i INT = 1000
WHILE @i > 0
BEGIN

INSERT test
SELECT ‘This is number ‘ + CONVERT(CHAR(5), @i)

SET @i -= 1
END

–WAIT to insure transaction completes
WAITFOR DELAY ‘0:00:05′;
GO

SELECT name,
recovery_model,
log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘pseudoSimple’;
GO

Once the backup is taken, SQL knows that the database can be recovered to a point in time so it will no longer truncate the log upon a checkpoint and the log will ONLY be truncated upon a transaction log backup. This is not exactly as described by MSDN, but does make sense, why let the log continue to grow when there is no way that the database can be recovered to a point in time.

The sample T-SQL code can be downloaded here.