Customized Transaction Log Backups

Transaction Log backups are important in a Production environment. It will make sure that you manage your log file size and keeping backups in case of a need to restore.

I am pretty much sure, most of you have scheduled transaction log backups. If you have scheduled Transaction log backups every 15 minutes, then you will see four log backups every hour and will result in nearly 100 backup files a day and you are looking at around 700 log backups per day. Unlike differential backups, you need all your lob backups to recover. Sometimes, you might have less or no transactions but still, there will be a log backup. 

Now the question is, Can we create transaction log backup when there is sufficient size. Yes, you can if you are running SQL Server 2017 or later. 

In sys.dm_db_log_stats Dynamic Management Function (DMF), there is a new column called log_since_last_log_backup_mb tells you what is the log file size after the last log backup. 

Using the following script, you can perform transaction log backups when the log file size is more than a specific size. 

DECLARE @log_since_last_log_backup_mb NUMERIC(9, 2)
DECLARE @ThreasholdSize INT = 25
DECLARE @folderName VARCHAR(30) = 'D:\DBBACKUP'
DECLARE @DatabaseName VARCHAR(30) = 'LB1'

SELECT @log_since_last_log_backup_mb = log_since_last_log_backup_mb
FROM sys.dm_db_log_stats(db_id(@DatabaseName))

IF @log_since_last_log_backup_mb > @ThreasholdSize
BEGIN
   DECLARE @fileName NVARCHAR(400) = @folderName + '\' + 

   @DatabaseName + SUBSTRING(REPLACE(CONVERT(VARCHAR, GETDATE(), 111), '/', '') 

   + REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', ''), 0, 13) + '.bak'

	BACKUP LOG [LB1] TO DISK = @fileName
	WITH NOFORMAT
		,NOINIT
		,SKIP
		,NOREWIND
		,NOUNLOAD
		,STATS = 10
END
ELSE
	PRINT 'No BACKUP'