SQL Sever 2017 – Smart Transaction Log Backup

Hello Friends,

SQL Server 2017 CTP 2.1 has been announced with some new features, one of them is Smart transaction log Backup i.e. exposing the log in mb since last log backup. You can download the SQL Server 2017 in the fastest way as explained by Avanish in his recent post. In this blog post we will learn about the new feature of SQL Server 2017 CTP 2.1 i.e. Smart Transaction Log Backup.

This approach will help us to implement the backup solution which will take log backups based on database activities and will also prevent the transaction log to grow continuously until the disk is full. We can implement the backup solution like “take the log backup if there is more than x MB log since last log backup”.

With SQL Server 2017 (CTP 2.1), a new DMF has been introduced i.e. sys.dm_db_log_stats. This DMF will require the database id as parameter. Using this DMV, we can get the information about how much log records have been generated since last log backup i.e. Amount of log need to be backed up by transaction log backup.

Step 1: Create a database and table along with some data:

USE [Master]
GO
CREATE DATABASE SmartLogBackup;
GO
USE [SmartLogBackup]
GO
CREATE TABLE tbl_SmartLogBackup
(
RecordID INT IDENTITY(1,1) NOT NULL,
FName VARCHAR(50),
LName VARCHAR(50),
City VARCHAR(100),
DeptID INT NOT NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX CIX_tbl_SmartLogBackup_RecordID ON tbl_SmartLogBackup(RecordID)
GO

DECLARE @Counter INT;
SET @Counter = 1;

SET NOCOUNT ON;

WHILE (@Counter<=100000)
BEGIN
	IF(@Counter%4=1)
		INSERT INTO tbl_SmartLogBackup VALUES('Ahmad','Osama','Gurgaon',3);
	ELSE IF(@Counter%4=2)
		INSERT INTO tbl_SmartLogBackup VALUES('Prince','Rastogi','Noida',4);
	ELSE IF(@Counter%4=3)
		INSERT INTO tbl_SmartLogBackup VALUES('Avanish','Panchal','Gurgaon',7);
	ELSE
		INSERT INTO tbl_SmartLogBackup VALUES('Amit','Yadav','Delhi',4);

	SET @Counter = @Counter + 1;
END

USE [SmartLogBackup]
GO
Select 
	DB_NAME(database_id) AS DBName, 
	recovery_model,
	total_log_size_mb,
	active_log_size_mb,
	log_since_last_log_backup_mb
from sys.dm_db_log_stats(DB_ID())
GO

Here log since last log backup column is showing value NULL because we have not taken the first full backup yet. Lets take first full backup.

Step 2: Take full backup and Modified some data to generate some log records:

USE [master]
GO
BACKUP DATABASE SmartLogBackup to DISK='D:\SmartLogBackup.BAK';
GO
USE [SmartLogBackup]
GO
UPDATE tbl_SmartLogBackup
	SET DeptID = 5
WHERE RecordID%4=0
GO
UPDATE tbl_SmartLogBackup
	SET City = 'Gaziabad'
WHERE RecordID%4=1
GO
USE [SmartLogBackup]
GO
Select 
	DB_NAME(database_id) AS DBName, 
	recovery_model,
	total_log_size_mb,
	active_log_size_mb,
	log_since_last_log_backup_mb
from sys.dm_db_log_stats(DB_ID())
GO

Here you can see that 9.726562 MB log has been generated due to the update operations.

Step 3: Take transaction log backup:

USE [master]
GO
BACKUP LOG SmartLogBackup
TO DISK='D:\SmartLogBackup_Log_1.trn';
GO
USE [SmartLogBackup]
GO
Select 
	DB_NAME(database_id) AS DBName, 
	recovery_model,
	total_log_size_mb,
	active_log_size_mb,
	log_since_last_log_backup_mb
from sys.dm_db_log_stats(DB_ID())
GO

Now you can see that log has been cleared after the log backup. This feature will also enable us for implementing alerts based on log usage. In my previous blog post, we have already learn the smart differential backup soltion provided by Microsoft in SQL Server 2017 CTP. Both of these features can be used to implement an Intelligent\Smart backup solution which will make life easier for DBAs.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook