Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Understanding SQL Server Recovery Models and Transaction Log Use


By:   |   Last Updated: 2018-04-02   |   Comments (3)   |   Related Tips: More > 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.

 sql server database properties recovery model

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.

sql server transaction log usage graph and report

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.

sql server transaction log usage graph and report

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.

sql server transaction log usage graph and report

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.

sql server transaction log usage graph and report

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.

sql server transaction log usage graph and report

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

sql server transaction log usage graph and report

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.

sql server transaction log usage graph and report

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.

sql server transaction log usage graph and report

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.

transaction log usage dbcc sqlperf

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'
			
transaction log usage dmv

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.


Last Updated: 2018-04-02


get scripts

next tip button



About the author
MSSQLTips author Dinesh Asanka 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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, February 07, 2019 - 9:31:29 AM - Silvana Siqueira Back To Top

 Excellent material !

Congratulations.


Tuesday, April 03, 2018 - 8:45:29 AM - M McDonald Back To Top

There is another very bad option to clear the transaction log

BACKUP LOG [DEMO_TransactionLog_Behavior] TO DISK='NUL:' 

Cheers


Tuesday, April 03, 2018 - 5:24:35 AM - Adel Yousuf Back To Top

Hi Dinesh

I would to thank you for your Professional Topic

Please could you talk about the bulk-logged model ?!

 

Regrds,

Adel


Learn more about SQL Server tools