SQL Server Brain Basher of the Week #023 - Truncation Issues

Log truncation is the process of freeing space in the logical log for reusing the space for new transactions. Depend on the database setting, log truncation can happen automatically, or it can be done manually, or it can happen as a part of another process. If SQL Server cannot find free space in the logical log, SQL Server expands the physical log file adding more virtual log for new transactions. If you are seeing a very large log file, the reason for that could be "no truncation" with the log file.

This week question is based on it;

How do you find out what prevents log truncation?

It is possible with querying sys.databases. This returns a column called log_reuse_wait_desc that indicates possible reasons for Log not getting truncated. There are many possible reasons, some of them are Checkpoint, Log backup, Database mirroring, Replication. For more info and all reasons, read this: The Transaction Log (SQL Server)

Here is a sample code for seeing this;

This code creates a database and table for testing.

USE master
GO

-- Create a test database
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'TestDatabase01')
 DROP DATABASE TestDatabase01;
GO

CREATE DATABASE TestDatabase01 ON  PRIMARY 
(  NAME = N'TestDatabase01', 
   FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\TestDatabase01.mdf' , 
   SIZE = 10240KB, 
   FILEGROWTH = 1024KB 
)
 LOG ON 
( NAME = N'TestDatabase01_log', 
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\TestDatabase01_log.ldf' , 
  SIZE = 5120KB, 
  FILEGROWTH = 10%
);
GO

-- Making sure it is set for FULL recovery model
-- This model requires Log Backup for log trunction
ALTER DATABASE TestDatabase01 SET RECOVERY FULL;
GO

--  Create a table for testing
CREATE TABLE TestDatabase01.dbo.Table01
( Column1 int IDENTITY(1,1) PRIMARY KEY,
  Column2 nvarchar(600)
);
GO

This code performs backup operation and checks for free space.

-- Connecting with the database and getting a fullbackup
USE TestDatabase01;
GO

BACKUP DATABASE TestDatabase01
  TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestDatabase01_Full.bak'
WITH INIT;
GO
 
-- Checking space in the files
SELECT name AS Name
 , type FileType
 , size * 8 /1024. as SizeinMB
 , FILEPROPERTY(name,'SpaceUsed') * 8 /1024. as SpaceUsedInMB
FROM sys.database_files;
GO

Note the space available, now let's insert some records and see again.

--  Inserting Data
INSERT INTO dbo.Table01 (Column2)
  VALUES('Here are some test data');
GO 5000

-- Checking space in the files
SELECT name AS Name
 , type FileType
 , size * 8 /1024. as SizeinMB
 , FILEPROPERTY(name,'SpaceUsed') * 8 /1024. as SpaceUsedInMB
FROM sys.database_files;
GO

As you see, Log file is filled and expanded too. Generally, we expect automatic log truncation with SIMPLE recovery model. Since this database is set with FULL recovery model, log will not be truncated until we perform the log backup. If we query sys.database, we can see the reason as LOG BACKUP.

-- Checking with sys.databases
SELECT name, log_reuse_wait_desc FROM sys.databases
WHERE name = 'TestDatabase01';
GO