The mysterious case of Pseudo SIMPLE RECOVERY model and the transaction log explosive growth

I created a  database to host a data set for some sql performance testing. The default Recovery Model was set to FULL. The test data load insert was executed and it was expected to take about 40 mins. 

About 15 mins into the the test data load insert  a scheduled full backup started. Up until the full backup started the transaction log growth rate was minimal. At the end of the FULL BACKUP -  the transaction log  started growing at a much quicker rate - with the same type of transactions.

What is going on here? Initially I was scratching my head - wondering why suddenly the transaction log growth rate exploded. Various options were considered.

This is what has been happening which caused the transaction log backup to grow unexpectedly.

Steps for analysis

      1)   The database is created with  the default Recovery set at  FULL. 

      2)   Even though the recovery model was FULL, the database behaved as if it’s using the SIMPLE recovery model, aka pseudo - simple recovery . One of the characteristics of the pseudo simple recovery is  the  the transaction log will automatically clear (to allow reuse and help prevent growth) whenever a checkpoint occurs.  Normal behaviour.  This situation continues until a database backup is performed and the database begins behaving like it’s in FULL Recovery .

      3)  If the first backup does not occur – which in our case didn’t – the checkpoint behaviour is based on a SIMPLE RECOVERY . when SQL Server begins backing up data pages, it also starts keeping track of transactions, via the transaction log. After it has backed up the last data page, it then also backs up all of the transactions that occurred during the data backup. 

           4) if we had actually created a backup before we started the performance testing – we would have seen  much larger transaction log growth from the outset.

            5) When the full  backup completed – the FULL RECOVERY model started – which then caused the transaction logs to grow at the rate they normally would as FULL RECOVERY. i.e where transaction logs are not cleared down at the checkpoint unless a LOG BACKUP occurs.

For a database to be in FULL RECOVERY it should have :  

1.  full backup to start the LSN chain
2.  the db recovery model should be FULL

So how can you verify this  set of circumstances?

create database MYDB
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'MYDB';

When you can see a full recovery model, but log_reuse_wait_desc shows NOTHING - this indicates the database doesn't require log backups for log truncation. The database is truncating the the logs post - transaction - which is how the simple recovery model works

SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = 'MYDB';

Use this query to check if a backup lsn exists for the database. If it's NULL - it means a log chain has not been started for this database. To start the log chain execute a FULL BACKUP

SELECT [last_log_backup_lsn] FROM sys.database_recovery_status WHERE [database_id] = DB_ID('MYDB')

Read more on topics and techniques used to troubleshoot the transaction log growth 

Slow SQL Server Backup and Restore with DBCC TRACEON (3004, 3605, -1)

Factors that can delay Log Truncation – LOG BACKUP

Log cannot be reused, see the log_reuse_wait_desc column in sys.databases – SQL Server Error 9002