It’s Simple!! Or Psuedo 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
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, pseudosSimple, 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;
GOIF EXISTS(SELECT * FROM sys.databases WHERE name = ‘pseudoSimple’)
BEGIN
DROP DATABASE
pseudoSimple
ENDCREATE 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:

name recovery_model log_reuse_wait_desc
pseudoSimple 1 NOTHING
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.

<style=”font-size: 12px;”>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:

name recovery_model log_reuse_wait_desc
pseudoSimple 1 NOTHING

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

<style=”font-size: 12px;”>–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

name recovery_model log_reuse_wait_desc
pseudoSimple 1 LOG_BACKUP

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.

Leave a Reply


Hit Counter provided by Sign Holders