Is Pseudo-Simple SQL Server Recovery a real thing?

Question

"Pseudo-Simple SQL Server Recovery" is term and scenario, I was just made aware of in a (now deleted) comment for the new question SQL Server Truncates Transaction Logs with Copy Only Backups

I went to the post Pseudo-Simple SQL Server Recovery Model October 7, 2019 by Rajendra Gupta and using some of the code there and some of my own did some testing.

Create the database (Rajendra's code)

CREATE DATABASE RecoveryModel;

and Validate it is in Full (Rajendra's code)

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

Do some work (Rajendra's code, modified slightly)

Use RecoveryModel
CREATE TABLE test(id INT);
GO 
INSERT INTO test
VALUES(1);
GO 5000

See how much log space is used (my code)

select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB'  --Addapted from https://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

 from sys.database_files
 order by type_desc Desc, name

We find that there log is filling. Run the work again and check the size, the log grows, no surprise.

Try running t-log (My code)

BACKUP LOG [RecoveryModel] TO  
DISK = N'E:\SQLBackups\RecoveryModel.trn' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD 
GO

It fails with message:

Msg 4214, Level 16, State 1, Line 8

BACKUP LOG cannot be performed because there is no current database backup.

Msg 3013, Level 16, State 1, Line 8

BACKUP LOG is terminating abnormally.

Nothing remotely simple about that, if you try to backup a database in Simple recovery. you get the message

Msg 4208, Level 16, State 1, Line 19

The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

Run a copy only backup (my code)

BACKUP DATABASE [RecoveryModel] TO  
DISK = N'E:\SQLBackups\RecoveryModel.bak' WITH NOFORMAT, INIT, COPY_ONLY,  
NAME = N'RecoveryModel-Full Database Backup', SKIP, NOREWIND, NOUNLOAD
GO

It runs fine, check the log space and it has not shrunk. Run the work load a few more time, and the log space continues to grow. Run a t-log backup and it continues to fail.

Run a differential backup (My code)

BACKUP DATABASE [RecoveryModel] TO  
DISK = N'E:\SQLBackups\RecoveryModel.dif' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  
NAME = N'RecoveryModel-Diff Database Backup', SKIP, NOREWIND, NOUNLOAD
GO

It fails just like the t_log

Msg 3035, Level 16, State 1, Line 13 Cannot perform a differential backup for database "RecoveryModel", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.

So what is "Pseudo-Simple" about this? The logs grow, t-log & differnatinal backups fail. You have a database in Full recovery, with no full backup.

Edit it looks like there is something specific to servers in my build that is causing results different than what everyone else is seeing. I have accepted Josh's answer.

Answer 1

See this article from Paul Randal, an expert on database recovery models: New script: is that database REALLY in the FULL recovery mode?

In particular this quote confirms the behavior of a "pseudo simple" recovery model:

...when you switch a database into the Full recovery mode, it actually behaves as if it’s in the Simple recovery mode until the log backup chain is established (this is commonly called being in ‘pseudo-Simple’).

Kimberly Tripp elaborates on the topic here: Database Maintenance Best Practices Part III – Transaction Log Maintenance

What that means is that SQL Server automatically clears inactive records from the transaction log once it knows that it no longer needs them. It no longer needs them to be stored in the log because no one is using the log

I don't totally agree with your statement "Nothing remotely simple about that" regarding the transaction log backup failing in the FULL recovery model prior to a FULL backup. That's just like the SIMPLE model (log backups not allowed). The error message is different because it's trying to tell the end user how to solve the problem.

I tried your test on SQL Server 2017, and running the copy-only backup consistently triggers an internal CHECKPOINT and clears the log (PercentFree increases for the log file).

On the initial run, PercentFree from the _log file is 65.04.
I ran the copy-only backup, and PercentFree jumped to 88.68.

I confirmed with the following extended events session that a CHECKPOINT was run at this time as well.

CREATE EVENT SESSION [checkpointage] ON SERVER 
ADD EVENT sqlserver.checkpoint_begin(
    ACTION(sqlserver.database_id)),
ADD EVENT sqlserver.checkpoint_end(
    ACTION(sqlserver.database_id))
ADD TARGET package0.event_file(SET filename=N'checkpointage')
WITH (STARTUP_STATE=OFF)
GO

Answer 2

Pseudo-simple means that a database in the full recovery model will behave as though it is in the simple recovery model until the first full backup is taken (the last Log Sequence Number that was backed up is recorded).

In other words, a database that is in the full recovery model requires a valid backup chain, and a valid backup chain requires a full backup to kick things off. Then you can perform log and differential backups in parallel to your heart's content, each of which are based off this initial full backup.

As you saw in the scripts you ran, there was no backup chain established until you ran your full (copy-only) backup. Once that ran, you started a backup chain, but because you did a copy-only backup, you confused things.

Differential backups are a shortcut to reduce the number of log backups you need to restore to a specific point in time. They are dependent on a full backup that is not COPY ONLY, because the copy-only backup does not reset the differential bitmap used for tracking which extents were modified since the last full backup.

Had you done a standard full backup (without COPY ONLY), your differential would have succeeded, and the database would no longer be in the pseudo-simple state. It was confused further by the fact that a differential backup would also have failed on a database in the simple recovery model without a full (non-copy-only) backup to kick off the chain.

Answer 3

I (OP on the question) was at a loss on why, "Pseudo-Simple SQL Server Recover" was working as defined in multiple posts, but I was not seeing it on my system with these tests.

In the research stage, I put the database in SIMPLE and recreated the test, the log file used size grew the same as it did in FULL, this suggested something with Control Transaction Durability but further testing ruled this out.

After more research and testing I figured it out! (Thank you to a coworker who helped me problem solve.)

• We are using 50MB as the start size for 'model' which means RecoveryModel starts the same. (Default SQL 2017 is 8MB)
• This was creating 4 VLFs at about 12.5MB each
• The workload I am using in the test above, creates about 2MB of data in the logs with each run.
• I was running the test work load 3 to 5 times (6 to 10MB of logs), before doing the COPY ONLY backup.
• The VLF used space was growing, but as it never filled up the first VLF, it stayed at status 2, and nothing was released.
• When the test seemed to give unexpected results I dropped the database and started a new test.
• In a follow up test I ran the workload to create ~15MB of logs, then ran the COPY ONLY and used space fell to 2MB, with the first VLF returning to Status 0 and the second VLF holding the last 2MB
• This can be validated by using the code below in addition to above to modify and test for yourself.

.

USE [RecoveryModel]
GO
DBCC LOGINFO

Related info talking about VLF's: Too many VLFs - How do I truncate them?