tlog_backup.sql

USE master;
GO

IF DB_ID('TestTlogBackup') IS NOT NULL
BEGIN
	ALTER DATABASE TestTlogBackup SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE TestTlogBackup;
END
GO

CREATE DATABASE TestTlogBackup;
GO

ALTER DATABASE TestTlogBackup SET RECOVERY FULL;
GO

-- Let's set autogrowth to 1 Mb
ALTER DATABASE TestTlogBackup MODIFY FILE ( NAME = N'TestTlogBackup_log', FILEGROWTH = 1024KB );
GO

USE TestTlogBackup;
GO

-- Size is the initial size for the log
-- In this case (my laptop) I left the default
-- setting in model, so it is around 1 MB, 400KB used
SELECT total_log_size_in_Mb = total_log_size_in_bytes / 1024.0 / 1024.0, 
	used_log_space_in_Mb = used_log_space_in_bytes / 1024.0 / 1024.0, 
	used_log_space_in_percent
FROM sys.dm_db_log_space_usage;
GO


-- Let's create a temp table: we will need it later 
-- to see the active portion of the log
IF OBJECT_ID('tempdb..#Loginfo') IS NOT NULL
	DROP TABLE #Loginfo;

CREATE TABLE #Loginfo (
	[RecoveryUnitId] int,
	[FileId] int,
	[FileSize] bigint,
	[StartOffset] bigint,
	[FSeqNo] int,
	[Status] int,
	[Parity] tinyint,
	[CreateLSN] numeric(25, 0)
);


-- The OP takes regularly FULL backups
-- Let's take at least one to initialize the log chain
-- Otherwise, the database is not really in full recovery
-- but it acts as if it was in simple recovery
BACKUP DATABASE TestTlogBackup TO DISK = 'NUL';
GO

-- Let's capture the active portion of the log
TRUNCATE TABLE #Loginfo;
INSERT INTO #Loginfo
EXEC('DBCC LOGINFO');

-- No transactions recorded so far: 1 VLF
SELECT MIN(CreateLSN) AS MinLSN, 
	MAX(CreateLSN) AS MaxLSN,
	COUNT(*) AS VLFCount,
	SUM(FileSize) AS TotalSize
FROM #Loginfo
WHERE Status = 2; -- active




IF OBJECT_ID('testNumbers') IS NOT NULL
	DROP TABLE testNumbers;
GO

-- Let's play with the database
CREATE TABLE testNumbers (
	num int identity(1,1),
	someColumn int NULL
)
GO



INSERT INTO testNumbers (someColumn)
SELECT TOP(100000) NULL
FROM sys.all_columns AS A
CROSS JOIN sys.all_columns AS B;
GO 10


-- Let's see if the log file grew 
-- It did: it's 131 MB now, with 111 MB in use
SELECT total_log_size_in_Mb = total_log_size_in_bytes / 1024.0 / 1024.0, 
	used_log_space_in_Mb = used_log_space_in_bytes / 1024.0 / 1024.0, 
	used_log_space_in_percent
FROM sys.dm_db_log_space_usage;
GO

-- Let's capture the active portion of the log again
TRUNCATE TABLE #Loginfo;
INSERT INTO #Loginfo
EXEC('DBCC LOGINFO');

-- We have 432 VLFs now
SELECT MIN(CreateLSN) AS MinLSN, 
	MAX(CreateLSN) AS MaxLSN,
	COUNT(*) AS VLFCount,
	SUM(FileSize) AS TotalSize
FROM #Loginfo
WHERE Status = 2; -- active


CHECKPOINT;

-- Let's take another FULL backup
BACKUP DATABASE TestTlogBackup TO DISK = 'NUL';
GO


-- The log file is always the same size
SELECT total_log_size_in_Mb = total_log_size_in_bytes / 1024.0 / 1024.0, 
	used_log_space_in_Mb = used_log_space_in_bytes / 1024.0 / 1024.0, 
	used_log_space_in_percent
FROM sys.dm_db_log_space_usage;
GO

-- Let's insert more rows
INSERT INTO testNumbers (someColumn)
SELECT TOP(100000) NULL
FROM sys.all_columns AS A
CROSS JOIN sys.all_columns AS B;
GO 10


CHECKPOINT;

-- Let's take another FULL backup
BACKUP DATABASE TestTlogBackup TO DISK = 'NUL';
GO


-- The log file grew again: out FULL backups are not
-- affecting the size of the log
-- Now we have 219 Mb used 
SELECT total_log_size_in_Mb = total_log_size_in_bytes / 1024.0 / 1024.0, 
	used_log_space_in_Mb = used_log_space_in_bytes / 1024.0 / 1024.0, 
	used_log_space_in_percent
FROM sys.dm_db_log_space_usage;
GO



-- Let's capture the active portion of the log again
TRUNCATE TABLE #Loginfo;
INSERT INTO #Loginfo
EXEC('DBCC LOGINFO');

-- We have 864 VLFs now
SELECT MIN(CreateLSN) AS MinLSN, 
	MAX(CreateLSN) AS MaxLSN,
	COUNT(*) AS VLFCount,
	SUM(FileSize) AS TotalSize
FROM #Loginfo
WHERE Status = 2; -- active
GO

CHECKPOINT;
CHECKPOINT;
GO

-- Let's try to back up the transaction log 
-- and see the size for the initial tlog backup
-- compared to the used size of the LDF
BACKUP LOG TestTlogBackup TO DISK = 'NUL';
GO


-- It's 231 Mb: very close to the used portion of the log
SELECT TOP 1 backup_size / 1024.0 / 1024.0
FROM msdb.dbo.backupset
WHERE database_name = 'TestTlogBackup'
	AND type = 'L'
ORDER BY backup_start_date DESC;
GO



-- Now the log file has plenty of space available
-- Now we have 239 Mb total, 30 Mb used
SELECT total_log_size_in_Mb = total_log_size_in_bytes / 1024.0 / 1024.0, 
	used_log_space_in_Mb = used_log_space_in_bytes / 1024.0 / 1024.0, 
	used_log_space_in_percent
FROM sys.dm_db_log_space_usage;
GO



-- Let's capture the active portion of the log again
TRUNCATE TABLE #Loginfo;
INSERT INTO #Loginfo
EXEC('DBCC LOGINFO');

-- We have just 1 VLF in use now
SELECT MIN(CreateLSN) AS MinLSN, 
	MAX(CreateLSN) AS MaxLSN,
	COUNT(*) AS VLFCount,
	SUM(FileSize) AS TotalSize
FROM #Loginfo
WHERE Status = 2; -- active
GO


-- OK, maybe the first transaction log backup
-- is a bit "special" and thing will be different 
-- from now on.
-- Let's insert more rows
INSERT INTO testNumbers (someColumn)
SELECT TOP(100000) NULL
FROM sys.all_columns AS A
CROSS JOIN sys.all_columns AS B;
GO 20



-- Now we have 239 Mb total, 217 Mb used
SELECT total_log_size_in_Mb = total_log_size_in_bytes / 1024.0 / 1024.0, 
	used_log_space_in_Mb = used_log_space_in_bytes / 1024.0 / 1024.0, 
	used_log_space_in_percent
FROM sys.dm_db_log_space_usage;
GO



-- Let's capture the active portion of the log again
TRUNCATE TABLE #Loginfo;
INSERT INTO #Loginfo
EXEC('DBCC LOGINFO');

-- 857 active VLFs
SELECT MIN(CreateLSN) AS MinLSN, 
	MAX(CreateLSN) AS MaxLSN,
	COUNT(*) AS VLFCount,
	SUM(FileSize) AS TotalSize
FROM #Loginfo
WHERE Status = 2; -- active
GO


CHECKPOINT;
CHECKPOINT;



-- Let's take another FULL backup
BACKUP DATABASE TestTlogBackup TO DISK = 'NUL';
GO




-- Still 239 Mb total and 217 Mb used
-- FULL backups not affecting the active 
-- size of the tlog, as expected
SELECT total_log_size_in_Mb = total_log_size_in_bytes / 1024.0 / 1024.0, 
	used_log_space_in_Mb = used_log_space_in_bytes / 1024.0 / 1024.0, 
	used_log_space_in_percent
FROM sys.dm_db_log_space_usage;
GO


-- Let's capture the active portion of the log again
TRUNCATE TABLE #Loginfo;
INSERT INTO #Loginfo
EXEC('DBCC LOGINFO');

-- Still 857 active VLFs
SELECT MIN(CreateLSN) AS MinLSN, 
	MAX(CreateLSN) AS MaxLSN,
	COUNT(*) AS VLFCount,
	SUM(FileSize) AS TotalSize
FROM #Loginfo
WHERE Status = 2; -- active
GO



CHECKPOINT;
CHECKPOINT;
GO

-- Let's take another transaction log backup
-- and compare its size to the used size of the LDF
BACKUP LOG TestTlogBackup TO DISK = 'NUL';
GO



-- It's 229 Mb: again very close to the size of the LDF
SELECT TOP 1 backup_size / 1024.0 / 1024.0
FROM msdb.dbo.backupset
WHERE database_name = 'TestTlogBackup'
	AND type = 'L'
ORDER BY backup_start_date DESC;
GO