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

Smart Transaction Log Backup 1

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

Smart Transaction Log Backup 2

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

Smart Transaction Log Backup 3

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

3 Comments on “SQL Sever 2017 – Smart Transaction Log Backup”

    1. A query regarding this feature.
      How can I configure smart T-log backups, do I have to write a script which will trigger the T-log backup based on the configured size or does it included in the Log Backup task\maintenance plan UI ?

      1. Hi Udham Singh,

        Sorry for late reply. As per my check this feature is not added in maintenance plan GUI yet.

        yes, you have to write a script which will trigger the T-log backup based on the configured size.

        Thank you!

        ~Prince Rastogi

Leave a Reply

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