This article gives an overview of the Pseudo Simple SQL Server Recovery Model. It also explores the conditions in which the database behaves in Pseudo mode.
Types of Recovery model in SQL Server
Recovery model property in the SQL Server database controls the transactions logging, supported backup types and database recovery scenarios such as point-in-time recovery. We have the following three recovery models for a SQL database.
- Full: We can perform point-in-time recovery and recover data without any data loss. It supports available backups such as full, log, differential
- Bulk-logged: It logs minimum log information for the bulk transactions such as BCP, CREATETEXT, and WRITETEXT. It works similar to a full recovery model and supports all available backups except that we cannot have a point in time recovery for the database in this recovery model
- Simple: It is the simplest form of a recovery model and truncates the logs once the transaction is committed. We cannot do point in time recovery in this as it does not supports log backups
You can refer article Understanding SQL Server database recovery models for detailed information about the recovery models.
Pseudo-Simple SQL Server Recovery Model
You might wonder that initially, we mentioned only three recovery models, but as per the name, it looks like a different recovery model. We will get to know this recovery model in the latter part of the article.
Let’s create a database:
|
1 |
CREATE DATABASE RecoveryModel; |
It creates a copy of the model database, and we can use sys.databases to check the current recovery model of this database.
|
1 2 3 4 |
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'RecoveryModel'; |
As you can see the database recovery model is FULL. In the full recovery model, it should truncate the logs after log backups only.
Let’s execute a sample workload and see do we have a database in full SQL Server Recovery Model.
Let’s create a table and insert few records in it.
|
1 2 3 4 5 |
CREATE TABLE test(id INT); GO INSERT INTO test VALUES(1); GO 1000 |
In the Full recovery model, it should wait for the transaction log backup before the truncation of the transaction log. We can check the status of the log reuse wait using the following query.
|
1 2 3 4 5 |
SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = 'RecoveryModel'; |
In the following screenshot, we can see a full recovery model, but log_reuse_wait_desc shows NOTHING. Nothing shows that the database does not require log backups to truncate the logs. The database is behaving as a simple recovery model in which it truncates the logs after the transaction.
The database should meet the following conditions in the full SQL Server Recovery Model:
- The database should have a full backup to start an LSN chain
- Database recovery model should be full
Execute the following query, and it returns whether the database is really in the full recovery model or not. The following query performs a check as per the following:
- If the database recovery model is full, does it have a full backup to validate the LSN chain?
- If the database recovery model is full without a full backup for the LSN chain, it shows output that the database is not behaving like a full recovery model. This condition of the recovery model is known as the pseudo-simple recovery model
In this query, we use tow system tables and views:
- Sys.database_recovery_status to get details of the last log backup LSN. If the database does not have a full backup, it shows NULL value else, and it will be the LSN of the full backup
- We use Sys.databases command to check the database recovery model
Query to check actual behavior of a SQL Server database
Execute the following query, and it gives the message whether database behaving similar to a database in full recovery model or not.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DECLARE @IsReallyFull BIT= 0; DECLARE @LastLogBackupLSN NUMERIC(25, 0); DECLARE @RecoveryModel TINYINT; SELECT @LastLogBackupLSN = [last_log_backup_lsn] FROM sys.database_recovery_status WHERE [database_id] = DB_ID('RecoveryModel'); SELECT @RecoveryModel = [recovery_model] FROM sys.databases WHERE [database_id] = DB_ID('RecoveryModel'); SELECT CASE WHEN @RecoveryModel = 3 THEN 'Database is in Simple recovery model' WHEN @RecoveryModel = 2 THEN 'Database is in Bulk-logged recovery model' WHEN @RecoveryModel = 1 AND @LastLogBackupLSN IS NOT NULL THEN 'Database is really in Full recovery model' WHEN @RecoveryModel = 1 AND @LastLogBackupLSN IS NULL THEN 'Database is in Pseudo simple recovery model' END AS Recoverymodel; |
In the output, you can see the database is not having similar to a full recovery model database.
Let’s understand the substantial term log sequence number and will come back to this part again.
Log sequence number
Execute the following query to check the last log backup LSN, and we get NULL value in it.
|
1 2 3 4 5 6 |
SELECT d.Name, d.recovery_model_desc, dr.last_log_backup_lsn FROM sys.databases d INNER JOIN sys.database_recovery_status dr ON d.database_id = dr.database_id WHERE d.database_id = DB_ID('Recoverymodel'); |
SQL Server backup internally works on the log sequence number. We might have different kinds of backups full, differential and log backup for the database in full recovery mode. All these backups are interconnected using the LSN’s. We require a full backup to start a backup chain, and subsequent backups follow this LSN chain.
In the following screenshot, we see the database in full SQL Server Recovery Model and having a log backup chain that follows the backups in a sequence full, the transaction log and differential backup.
Let’s take full database backup using the following query, and it starts the backup chain for the full recovery model.
|
1 2 |
BACKUP DATABASE [RecoveryModel] TO DISK = N'E:\Backup\RecoveryModel.bak' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO |
Once the full backup command, execute the command to check the last log backup LSN. Previously we have a NULL value, but now we can see LSN number in this column.
Let’s execute the workload on this database and check the log holding reason. It shows the reason Log_Backup that means log will be truncated only after the log backup.
Execute the Query to check the actual behavior of a SQL Server database. In the output, we get the message that the database is really in full recovery mode.
Let’s switch the recovery model from FULL to Simple and do the transactions and revert to Full again. Once we change the SQL Server Recovery Model to Simple, it breaks the log chain. We changed the recovery model back to full, but it does not recreate the LSN chain. It’s required to take another full backup, so that new backup chain can be formed, and you can start subsequent log backups. The database behavior during the log chain break scenario is called the pseudo-simple recovery model. In this model, the database behaves similar to a simple SQL Server Recovery Model and truncates the logs once the transaction is committed. We need to take a full backup so that SQL Server can prepare the log chain and subsequent backup can work.
In the article, Understanding Log Sequence Numbers for SQL Server Transaction Log Backups and Full Backups, we explored the log sequence number and its relation with the log backup and full backups. Until we explored that if we change the database recovery model from full to simple, it breaks the LSN chain.
We can also change the recovery model from bulk-logged to simple SQL Server Recovery Model. It also breaks the LSN chain and works similar to a Pseudo simple recovery model database.
Should you worry about if a database is in a Pseudo simple recovery model? Yes, it should be a concern as it affects the database recovery. You cannot perform point in time recovery because log backups will not work once the LSN chain breaks. Ideally, you should never change the database recovery model from full to simple. Usually, dba changes the recovery model to avoid excessive log growth during certain operations, but it is not recommended. You should plan the frequency of transaction log backups in such a way that it does not increase the huge log space.
Conclusion
In this article, we explored the scenario in which a database can behave like a pseudo-simple SQL Server Recovery Model. You should avoid the recovery model change from full to simple. If you have to do it, please take a full database backup immediately to set up an LSN chain for the subsequent backups.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023