SQL Server Transaction Log file behaviour in Full Recovery Mode

In a previous blog post, I have explained different types of recovery models in SQL Server databases. During the explanations, I have stated that the transaction log data will be truncated during certain backup types. We will look into this more closely and see how it works.

Note: Even the details are removed from the transaction log file, the physical size may not change unless you issue a DBCC SHRINKFILE command. Only the file space will be re-used once the details are truncated.

We will start with creating a sample database named 'SqlTransactionLogExample'

      
USE master;
GO 

CREATE DATABASE [SqlTransactionLogExample];

--== Makesure the database is in "Full" recovery model ==--

SELECT 
	[name]
	,DATABASEPROPERTYEX([name],'Recovery') AS recovery_model
FROM
	sys.databases
WHERE
	[name] = 'SqlTransactionLogExample'
    

We will create one table and insert records (1,000,000 rows to see a significant change in the file size).

   
USE SqlTransactionLogExample;
GO

CREATE TABLE dbo.UidInformation(
	Id				UNIQUEIDENTIFIER NOT NULL
	,PayLoad		CHAR(1000) NOT NULL CONSTRAINT [DF_SampleTable] DEFAULT (SPACE(1000))
)

INSERT INTO dbo.UidInformation (
	Id
	,PayLoad
)
SELECT	
	NEWID() AS Id,
	([100000].Num 
	+ [10000].Num  
	+ [1000].Num 
	+ [100].Num 
	+ [10].Num 
	+ [1].Num) AS PayLoad
FROM (
	SELECT 1 AS Num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
	SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
	UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL 
	SELECT 0
) AS [1]
CROSS JOIN (
	SELECT 10 AS Num UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL 
	SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL 
	SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90 UNION ALL 
	SELECT 0
) AS [10]
CROSS JOIN (
	SELECT 100 AS Num UNION ALL SELECT 200 UNION ALL SELECT 300 
	UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 
	UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900 
	UNION ALL SELECT 0
) AS [100]
CROSS JOIN (
	SELECT 1000 AS Num UNION ALL SELECT 2000 UNION ALL SELECT 3000 
	UNION ALL SELECT 4000 UNION ALL SELECT 5000 UNION ALL SELECT 6000 
	UNION ALL SELECT 7000 UNION ALL SELECT 8000 UNION ALL SELECT 9000 
	UNION ALL SELECT 0
) AS [1000]
CROSS JOIN (
	SELECT 10000 AS Num UNION ALL SELECT 20000 UNION ALL SELECT 30000 
	UNION ALL SELECT 40000 UNION ALL SELECT 50000 UNION ALL SELECT 60000 
	UNION ALL SELECT 70000 UNION ALL SELECT 80000 UNION ALL SELECT 90000 
	UNION ALL SELECT 0
) AS [10000]
CROSS JOIN (
	SELECT 100000 AS Num UNION ALL SELECT 200000 UNION ALL SELECT 300000 
	UNION ALL SELECT 400000 UNION ALL SELECT 500000 UNION ALL SELECT 600000 
	UNION ALL SELECT 700000 UNION ALL SELECT 800000 UNION ALL SELECT 900000 
	UNION ALL SELECT 0
) AS [100000]
WHERE(
	[100000].Num 
	+ [10000].Num  
	+ [1000].Num 
	+ [100].Num 
	+ [10].Num 
	+ [1].Num
) BETWEEN 1 AND 1000000
ORDER BY ([100000].Num + [10000].Num  + [1000].Num + [100].Num + [10].Num + [1].Num)

Now we will inspect the number of entries in the transaction log.

   
  SELECT COUNT(*) FROM sys.fn_dblog (NULL,NULL)
  --Returns 2511475

Note: Record count may be varied in the transaction log of your database.

We will check the size of the transaction log file 

Now we will take a full database backup

USE master;
GO

BACKUP DATABASE [SqlTransactionLogExample] TO DISK = 'D:\_SQL_BACKUP\SqlTransactionLogExample_FullDB_Backup.bak'

Now we will inspect number of entries in the transaction log file again.

USE SqlTransactionLogExample
GO

SELECT COUNT(*) FROM sys.fn_dblog (NULL,NULL)

--Returns 2511475

Note: There is a slight increment of the record count since there are new entries written to the log file during the database backup.

We will now inspect the transaction log entries

  
SELECT COUNT(*) FROM sys.fn_dblog (NULL,NULL)
-- Returns 2511545

We will now take a backup of the transaction log 

  
BACKUP LOG [SqlTransactionLogExample] TO 
DISK = 'D:\_SQL_BACKUP\SqlTransactionLogExample_LogBackup_AfterInsert.bak'

And if we inspect the number of entries in the log file it has drastically reduced.

  
SELECT COUNT(*) FROM sys.fn_dblog (NULL,NULL)
--Returns 10

However, the space consumed by the log file is still the same. This is the reason I have mentioned it previously. In order to release the space, we have to issue the DBCC SHRINKFILE command.

--This is to find the name of the logical log file name
SELECT * FROM sys.sysfiles
--SqlTransactionLogExample_log
DBCC SHRINKFILE('SqlTransactionLogExample_log',1)

Hope this will be helpful to you.