Rajendra Gupta

Pseudo-Simple SQL Server Recovery Model

October 7, 2019 by

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:

It creates a copy of the model database, and we can use sys.databases to check the current recovery model of this database.

Recovery Model

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.

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.

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.

log_reuse_wait_desc status in sys.databases

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.

In the output, you can see the database is not having similar to a full recovery model database.

Pseudo simple recovery model

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.

Log sequence number

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.

database backups

Let’s take full database backup using the following query, and it starts the backup chain for the full recovery model.

Full DB backup

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.

FUll backup and LSN

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.

Log_reuse_wait_desc status as 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.

Pseudo simple SQL Server Recovery Model

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.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views