We are aware of three recovery model (simple,bulk and full) and each recovery model's characteristics in SQL Server. But do they show this characteristic always??
No, If we have not taken a full backup after changing database from simple to bulk/Full or a database is created in bulk/Full recovery model, Database will not wait for a log backup to truncate log file but it truncates on checkpoints which is behavior of database in simple recovery model and this mode is sometimes referred as pseudo simple recovery model(not documented). If you try to make a log backup of database in this state it will fail with below message
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
That makes sense right , Database engine show this behavior as we are aware that it requires a full backup to do a point in time recovery, then why let the log continue to grow when there is no way that the database can be recovered to a point in time
How can i spot if my database is in pseudo simple mode?
There is no direct way to find if a database is in pseudo simple mode, We can find this by checking last_log_backup_lsn column in sys.database_recovery_status , if this column in NULL it means a transaction log back up cannot be performed because either the database is in SIMPLE recovery or there is no current database backup and by checking recovery_model_desc column in sys.databases whether the database is in Full or BULK logged model
Below script will help us in identifying database in pseudo simple
recovery model
select db.name,db.recovery_model_desc,drs.last_log_backup_lsn from sys.database_recovery_status drs join sys.databases db on db.database_id=drs.database_id
where db.recovery_model in (1,2) and drs.last_log_backup_lsn IS NULL
In my instance below are the databases in pseudo recovery model
