Is Pseudo-Simple SQL Server Recovery a real thing?

"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 DAT

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

StackExchange Database Administrators Q#255969, answer score: 5