Relational databases are designed to track changes introduced to a database by data modification language (DML) commands. The fundamental reason for this construct is to ensure that changes are durable and that they can be rolled back reliably. The typical DML command used in SQL are INSERT, UPDATE and DELETE. When INSERT introduces new rows to a database table, the database engine must persist the activity physically in an efficient manner.
This means the change must be recorded quickly in a log file (log buffer first) while the actual data blocks are still in memory until a checkpoint occurs. This also goes for UPDATE and DELETE operations. Attempting to persist changes made in memory to data files directly would not be efficient. This log file or Transaction Log File is thus very important for the activities of a relational database system such as SQL Server.
In this article we illustrate how the transaction log can consume much more space that the actual data contained in the database. We also demonstrate that this happens because the transaction log captures and stores a history of changes to the database compared to the data files which store the end state after changes are done. Also covered are a few recommendations on how to manage this transaction log growth proactively and reactively.
Experiment: Log Growth vs. Data Growth
The first step is to show an example of log growth compared to data file growth when performing simple DML operations. The database used for the experiment has been designed to make the impact of the steps apparent. The code in Listing 1 creates the database and enables FULL RECOVERY mode.
-- Listing 1: Create Database with Small Filegrowth for Illustration
/****** Object: Database [DB01] Script Date: 14/05/2022 9:38:51 am ******/CREATE DATABASE [DB01]
CONTAINMENT = NONE
( NAME = N'DB01', FILENAME = N'C:MSSQLDataDB01.mdf' , SIZE = 4MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1KB )
( NAME = N'DB01_log', FILENAME = N'E:MSSQLLogDB01_log.ldf' , SIZE = 512KB , MAXSIZE = 2048GB , FILEGROWTH = 1KB )
ALTER DATABASE DB01 SET RECOVERY FULL;
To ensure there is a baseline, we use Listing 2 to check the physical size of the data and log files as well as the space used. Figure 1 shows the result sets obtained from executing Listing 2.
-- Listing 2: Check Physical Log Growth on Database
SELECT name, physical_name, size*8 , max_size
WHERE name like 'DB01';
-- Check Data File Space on Database
-- Check Log Space on Database
DB_NAME(database_id) [Database Name]
,total_log_size_in_bytes/1024 [Total Log Size (KB)]
,used_log_space_in_bytes/1024 [Used Log Space (KB)]
,used_log_space_in_percent [Used Log Space (]
FROM sys.dm_db_log_space_usage ;
Introduction Relational databases are designed to track changes introduced to a database by data modification language (DML) commands. The fundamental reason for this construct is to ensure that changes are durable and that they can be rolled back reliably. The typical DML command used in SQL are INSERT, UPDATE and DELETE. When INSERT introduces new […]