Closure
Closure
Home » Databases » Microsoft SQL Server » What is the Transaction Log and what is it used for in SQL Server

What is the Transaction Log and what is it used for in SQL Server

What is the Transaction Log and what is it used for in SQL Server
, , , , Leave a comment
What is the Transaction Log and what is it used for in SQL Server
Latest posts by Stratos Matzouranis (see all)

In this article we will analyze what it is Transaction Log in Microsoft SQL Server, what it is for and what to look out for.

What is Transaction Log

Every database we make except the data files containing its data, it also creates the transaction log files whose job it is to record all the transactions that take place in it.

The transaction log contains every change that has been made to the database such as insert, update and delete which are DML (Data Manipulation Language) actions as well as DDL (Data Definition Language) actions such as alter and create.

What is the Transaction Log for?

The transaction log is used in the following tasks:

  • For the rollback of a transaction.
  • For the recovery of the SQL Server instance during startup.
  • To restore the database to Point in Time (at a specific moment in time).
  • For High Availability and Disaster recovery solutions such as Availability Groups, Log shipping and transactional replication.

What are Virtual Log Files (VLF)

The transaction log consists of physical files on disk called transaction log files. These in turn are divided into smaller subgroups called Virtual Log Files or else VLF. Every time there is an active transaction in this database it is recorded in a Virtual Log File. Every time the transaction log fills up and extends, new VLFs are created in turn. The very large number of VLFs can lead to delays in instance startup, log backups and restores.

If the extend in the transaction log file has not been limited, the file will grow as long as it has space on the disk.

What is the Transaction Log and what is it used for in SQL Server
What is the structure of the Transaction Log

How do you free up space from the transaction log

The transaction log continuously empties and fills the Virtual Log Files. SQL Server itself periodically flushes any Virtual Log Files that no longer record an active transaction (eg completed). If this was not done, after a certain point the transaction log file and the physical disk would be full of space.

According to Recovery Model that we have set the database to work also changes how the transaction log will be freed, specifically:

  • If the base works on Simple recovery model (which does not have the ability to Point in Time restore), then it runs intermittently checkpoint recording the changes to the datafiles and frees the transaction log from the inactive Virtual Log Files (whose transactions have been committed or rolled back).
  • If the base works on Full recovery model (which has the ability to Point in Time restore), then again it is done checkpoint and frees the transaction log from inactive Virtual Log Files but after a transaction log backup has been performed first.

We must not forget that when the database performs a checkpoint, it frees the transaction log space that is free for use but does not reduce its physical size on disk. To reduce it we should use the DBCC SHRINKFILE function. We will analyze the way the whole process is done in the next article.

Sources:

Share it

Leave a reply