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:

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:

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:

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.


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