SQL Transaction Log backups

This type of backup is only possible with Full (not Simple) recovery model. A Log backup is an incremental backup and contains data that have not been included since the last Log backup. Below is a script to create Log backups

BACKUP LOG db_name TO DISK = 'file_name.TRN'

The Log Sequence Number (LSN) increments in the Log chain with each Log backup (assuming database has data changed between each backup). The Log chain is a contiguous series of logs that details the changes made to the database to a point in time. A Log backup chain always resets with a Full or Differential backup and continues until the log chain is broken.

Examples of what can break the log chain are:

  1. Changing database Full to Simple recovery model
  2. Taking an extra Full backup without ‘COPY_ONLY’

source – transaction log backups

Transaction logs

Transaction log is a special file associated with a database that holds data logs of all the changes applied to a database (except SELECT INTO or BULK IMPORT transactions). Each transaction log record has a unique Log Sequence Number (LSN). Transaction log file will continue to grow in size unless it is explicitly shrunk or regular Log backups are set up.

Transaction logs truncation

After a Log backup, the space within the Transaction log file (.trn) is reused. If Log Backups are not taken regularly, the file size grows in size. By default Transaction, log files do not ‘AUTO_SHRINK’ in size.

Transaction log truncation will not occur during a Full backup even if you take Log backups. Truncation occurs at the first Log backup after an LSN re-base (Full backup) 

To force a transaction log file to shrink use:

ALTER DATABASE db_name
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE ('db_log_filename', 200)
GO
ALTER DATABASE db_name
SET RECOVERY FULL

Note: Forcing a transaction log to shrink will break the log chain and destroys the ability to restore the transactions and risks data loss!

When using Full Recovery model taking a Log Backup can prevent the Transaction log file from growing. Note that taking a Full backup will not reclaim space in the transaction log file. However, taking Full backup will mean all previous Log backup are not needed for a point in time recovery. Example of how to reclaim space with Log backup :

USE db_name
BACKUP DATABASE db_name
TO DISK=N'Backupname.bak'
WITH INIT,NAME = N'Full Backupname'; 
GO
BACKUP LOG db_name TO DISK = 'TransactionBackupName.TRN'
GO

Note: you may need to suspend High Availability / Mirroring for the database before being able to shrink the Transaction log file.

Transaction log space use

Transaction log file only grows when all the internal space is used up. Space is cleared from the transaction log file as a result of a forced shrink or Log backup operation. Transaction logs that occur after such operation will not force transaction log to grow until the newly cleared space is used up.

Transaction log file growth behavior and internal Transaction log file space usage.

Below is a query that can be used to investigate log space usage:

-- List database log space use
DBCC SQLPERF(LOGSPACE)

source – transaction log file not shrinking
source – transaction log backup during full backup

Demo: Transaction log backup and restore

  • Shows transaction log being taken after Full backup
  • Shows recovery example
-- Create Database BackupDemoDB
CREATE DATABASE [BackupDemoDB]
ALTER DATABASE [BackupDemoDB] SET RECOVERY FULL

-- Create Full backup
BACKUP DATABASE [BackupDemoDB] TO DISK = N'C:\TransactionLogExample\BackupDemoDB_Full01.bak'
WITH NOFORMAT, NOINIT, NAME = N'BackupDemoDB-Full', SKIP, NOREWIND, NOUNLOAD, STATS = 10

-- Log Backup
BACKUP LOG [BackupDemoDB] TO DISK =  N'C:\TransactionLogExample\BackupDemoDB_TransLog01.trn'

-- Create Table in Database with Transaction
USE BackupDemoDB
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[RealTempTable]') AND TYPE IN (N'U'))
	DROP TABLE [dbo].[RealTempTable]

-- Create data
CREATE TABLE RealTempTable (ID INT)

-- Repeat untill the log file increases in size
INSERT INTO RealTempTable (ID)
SELECT TOP 50000 ROW_NUMBER()
OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b

-- log file space use
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files;

-- Log Backup
BACKUP LOG [BackupDemoDB] TO DISK =  N'C:\TransactionLogExample\BackupDemoDB_TransLog02.trn'

-- Note that second transaction log is very small due to not many changes
BACKUP LOG [BackupDemoDB] TO DISK =  N'C:\TransactionLogExample\BackupDemoDB_TransLog03.trn'

-- Inspect bakup and transaction log backup files
--RESTORE HEADERONLY FROM DISK=N'C:\TransactionLogExample\BackupDemoDB_Full01.bak';
--RESTORE HEADERONLY FROM DISK=N'C:\TransactionLogExample\BackupDemoDB_TransLog02.trn';
--RESTORE HEADERONLY FROM DISK=N'C:\TransactionLogExample\BackupDemoDB_TransLog03.trn';

-- Restore Diff database after database drop
DROP DATABASE [BackupDemoDB]
RESTORE DATABASE [BackupDemoDB] FROM DISK = N'C:\TransactionLogExample\BackupDemoDB_Full01.bak' WITH NORECOVERY;
RESTORE LOG [BackupDemoDB] FROM DISK = N'C:\TransactionLogExample\BackupDemoDB_TransLog01.trn' WITH NORECOVERY;
RESTORE LOG [BackupDemoDB] FROM DISK = N'C:\TransactionLogExample\BackupDemoDB_TransLog02.trn' WITH NORECOVERY; 
RESTORE LOG [BackupDemoDB] FROM DISK = N'C:\TransactionLogExample\BackupDemoDB_TransLog02.trn' WITH RECOVERY; 
GO

Demo: Only Log backup shrinks Transaction log file

-- Create Database BackupDemoDB
CREATE DATABASE [BackupDemoDB]
ALTER DATABASE [BackupDemoDB] SET RECOVERY FULL

-- Create Full backup
BACKUP DATABASE [BackupDemoDB] TO DISK = N'C:\TransactionLogExample\BackupDemoDB_Full01.bak'
WITH NOFORMAT, NOINIT, NAME = N'BackupDemoDB-Full', SKIP, NOREWIND, NOUNLOAD, STATS = 10

-- Create Table in Database with Transaction
USE BackupDemoDB
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[RealTempTable]') AND TYPE IN (N'U'))
	DROP TABLE [dbo].[RealTempTable]

-- Create data
CREATE TABLE RealTempTable (ID INT)

-- Repeat data insert
INSERT INTO RealTempTable (ID)
SELECT TOP 50000 ROW_NUMBER()
OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b

-- log file space before Log backup
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files;

-- log file space use after log backup, space reclaimed
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files;
BACKUP LOG [BackupDemoDB] TO DISK =  N'C:\TransactionLogExample\BackupDemoDB_TransLog01.trn'

-- log file space use after full backup, no space reclaimed
BACKUP DATABASE [BackupDemoDB] TO DISK = N'C:\TransactionLogExample\BackupDemoDB_Full02.bak' WITH NOFORMAT, NOINIT, NAME = N'BackupDemoDB-Full', SKIP, NOREWIND, NOUNLOAD, STATS = 10
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files;

-- log file space use after Log backup, space reclaimed
BACKUP LOG [BackupDemoDB] TO DISK =  N'C:\TransactionLogExample\BackupDemoDB_TransLog03.trn'
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files;

source – clearing transaction log backups

Leave a Reply

Your email address will not be published. Required fields are marked *