Understanding SQL Server Recovery Models and Transaction Log Use

By: Dinesh Asanka   |   Updated: 2018-04-02   |   Comments (5)   |   Related: > Recovery Models

Problem

The transaction log of the SQL Server database is critical component for the database. In this tip we take a look at how the recovery model impacts the database transaction log usage.

Solution

Every SQL Server database will have at least two files: the data file and the log file. We all can understand the need of the data file, but not everyone understands the role of the log file also known as transaction log file. In simple terms, every SQL Server database (whether it is on-premises, Azure SQL Server, Azure SQL Data Warehouse or Parallel Data warehouse) records all transactions and database modifications that are made by each transaction in the transaction log. Needless to say, the transaction log is a critical component of a SQL Server database.

To learn more about the transaction log take a look at this https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server. Here is more on the transaction log architecture https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide.

What are the impacts to the SQL Server Transaction Log based on the Recovery Model?

• SQL Server Recovery Model Selection - Simple or Full
• Configure the SQL Server Database Recovery Model
• SQL Server Transaction Log Use in SIMPLE and Full Recovery Model
• Clear the SQL Server Transaction Log
• SQL Server Transaction Log Usage Information
• SQL Server Transaction Log Best Practices

SQL Server Database Recovery Model

The recovery model of the database will define how transactions are logged and kept in the transaction log. This database property will define how the transaction log clears older transactions and the backup and restore operations that are available. Also, the recovery model defines whether the user can enable features such as log shipping, point in time recovery, etc. It is important to note that the recovery model setting for a database cannot be changed for Azure SQL databases.

There are three recovery model options: simple, full and bulk-logged. Typically, simple and full recovery models are the most used recovery models, so that is what we will cover in this tip.

SQL Server Simple Recovery Model

When the simple recovery model is in place, transaction log file entries will be available only while the transaction is active. Once the transaction is committed, log entries can be overwritten by other transactions, so the transaction log space is reclaimed for new transactions.

In this configuration, point in time recovery is not possible. Point in time recovery is an essential feature in the database system, where it allows users to recovery to a given time from accidental data deletes or other issues. However, in the case of development and QA environments, the simple recovery model is most often used.

SQL Server Full Recovery Model

In the case of the full recovery model, log file entries are not cleared, but committed transactions will be marked as inactive and will sit in the transaction log until the transaction log is backed up.  So for the full recovery model, the transaction log will grow as new transactions are created and the transaction log entries are not cleared automatically.

In the case of a production environment, it is essential to configure your database with full recovery model, as you never know when a point in time recovery option is needed.

To learn more about recovery models, read this https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server.

Configure the Database Recovery Model in SQL Server

When you create a database and you don't specify the recovery model, by default, the new database will use the recovery model of the model system database. This means that if you want to change the default recovery model for new databases, you need to change the recovery model of the model database. It is important to note that, changing the recovery model of the model database does NOT change the recovery model of existing databases.

Here is the command to create a database and it will use the default values.  On my system, the recovery model for the model database is set to full.  This will create a database with a data file of 8MB and a log file of 8MB and both data and log files have an autogrow setting of 64MB (these are defaults on my system).

--Database Creation
CREATE DATABASE DEMO_TransactionLog_Behavior
GO

After the database is created, we can change the recovery model to simple as follows.

--Changing the Recovery model to SIMPLE
USE [master]
GO
ALTER DATABASE DEMO_TransactionLog_Behavior SET RECOVERY SIMPLE WITH NO_WAIT
GO			

Or the recovery model can be changed to simple using SQL Server Management Studio (SSMS).  Right click on the database name, select Properties and go to the Options page.  The recovery model can be changed as shown in the below image.

 

I changed the recovery model to simple for our first test.

Let's create a sample table.

USE [DEMO_TransactionLog_Behavior]
GO
CREATE TABLE SampleData
(ID BIGINT IDENTITY,
NAME CHAR(3000)
)

SQL Server Transaction Log Use in SIMPLE Recovery Model

Let's insert some data to the new table.

-- script to insert 1000 rows of data
INSERT INTO SampleData (NAME)
VALUES ('Sample Name')
GO 1000 -- Insert will be executed for 1000 times			

Let's verify the log file size and its usage after the execution of the above script. These reports are taken from the SQL Server Management Studio reports (right click on database name > Reports > Standard Reports > Disk Usage).

Below we can see the log file is 8 MB and around 90% of the log file is unused.

Let's execute the insert script again and examine the same results as before.  You will observe that the transaction log file size was not modified and usage percentage is similar as before. One of the things we can see is that the data file grew to 72MB, but the log file remains 8MB.

Let's run the insert script again and examine this again to confirm the what we are seeing is correct.  We can see that the transaction log file grew a little more.

This verifies that the SIMPLE recovery model will reuse the transaction log space after the transaction is committed.

SQL Server Transaction Log Use in FULL Recovery Model

Let's perform the same test when the database recovery model is set to FULL. Please note that a full backup needs to be executed for the recovery model to take effect.

--Changing the Recovery model to FULL
USE [master]
GO
ALTER DATABASE DEMO_TransactionLog_Behavior SET RECOVERY FULL WITH NO_WAIT
GO			

Create full backup:

BACKUP DATABASE DEMO_TransactionLog_Behavior 
TO DISK = N'C:\Backup\DEMO_TransactionLog_Behavior.BAK'
GO

After the full backup is executed, let's run the insert script again. Now it is very evident that log used percentage is increased suddenly to 73% which did not clear the log automatically as in the case of the SIMPLE recovery model.

Let's run the insert script again. Though the transaction log used percentage is reduced, it is important to note that log file size has jumped to 72MB from 8MB. So now we are using 14% of 72MB vs. 73% of 8MB.

Let's run the insert script again and we can see the transaction log usage has increased.

That shows that in case of the FULL recovery model the transaction log usage will continue to increase.  Once it gets to a point where there is not enough free space in the transaction log to support a new transaction, it will grow another 64MB.

Clear SQL Server Transaction Log in Full Recovery Model

Let's assume the transaction log grows for a while as shown below.

In case of the FULL recovery model, there are a couple of options to clear the log.  One is a good option (log backup) and the other is not so good (change recovery model to simple) especially for production databases.

The best option is to create a transaction log backup as follows:

BACKUP LOG [DEMO_TransactionLog_Behavior] 
TO DISK = N'C:\Backup\DEMO_TransactionLog_Behavior.trn' 
WITH NOFORMAT, NOINIT,  
NAME = N'DEMO_TransactionLog_Behavior-Full Log Backup'
			

After the transaction log backup was taken, we can see the transaction log file usage has decreased as shown below.

This shows that log file is cleared, but you will notice that log file size was not reduced and it is still 72MB. If you need to resize the transaction log you will need to use DBCC SHRINKFILE to do this.

SQL Server Transaction Log Usage Information

There are other ways of getting the log file usage apart from the SQL Server Management Studio (SSMS) reports.

DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS
			

The above script will give you the log file usage of all the databases as shown below.

Starting with SQL Server 2012, we can also run the following.

SELECT a.*
FROM sys.dm_db_log_space_usage a
INNER JOIN sys.databases b on a.database_id = b.database_id
WHERE b.name = 'DEMO_TransactionLog_Behavior'
			

SQL Server Transaction Log Best Practices

Transaction log backups should be scheduled to run frequently in a production database system, to allow for point in time recovery. If the transaction log backups are not scheduled for a database in FULL recovery model, the transaction log will continue to grow. This also will increase the time of the full backup when it does run. Also, the full backup size will be large and will consume more disk space. Also, the restore operation could take more time.

Conclusion

The simple recovery model is better suited for environments such as Development or QA where data recovery is not a requirement. Whereas the full recovery model is essential for Production environments where you need point in time recovery for data.

In the case you have configured your database to be in the full recovery model, make sure that you have enabled transaction log backups.

Next Steps

• If you have not yet implemented log backups in production, it is time to implement transaction log backups in production.
• If the recovery model is full in development or QA, be quick to change the recovery model to simple to avoid unnecessary maintenance tasks and disk space usage.

About the author

Dinesh Asanka is a 10 time Data Platform MVP and frequent speaker at local and international conferences with more than 12 years of database experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips