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.
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
3 Comments on “SQL Sever 2017 – Smart Transaction Log Backup”
Nice feature to have and well explained, thank you.
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 ?
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.