SQL SERVER – Script – Transaction Log Backups Based on Growth / Size

6 years ago

While doing a comprehensive health check of the SQL Server, one of the things I check is the backup plan for the production databases. Many times, I have run into a situation where a customer says “Transaction log file of the database has grown more than expected. I want to know the reason why and how to reclaim space and how to avoid such situations”. Let’s break it down into two parts and then try to solve it.

Why did the T-log grow so huge?
How to avoid such situation?

For the first point, we already have many articles on the internet and few of them are by Microsoft

My few earlier blogs on the same topic can be found below,

Most of the articles available online talks about the importance of taking timely T-Log backups. Now, what if you are in a situation where we have the following conditions,

Shortage of disk space
Unpredictable big transaction runtimes

In such situations, choosing a schedule to take T-log backups can get challenging. Well, there is an alternate to tackle such challenges. “Try to take T-Log backups based on Growth”. For example: Take T-log backup as soon the T-log size reached 5gb.

This approach can be taken as an additional step to the regular T-log backups which you have already scheduled.

WORKAROUND/SOLUTION

You can use the below Stored Procedure to create a job and schedule it. This job will also provide you shrink the Transaction log file as soon as the Transaction log backup completes.

create proc sp_log_backup_and_shrink
as
begin
DECLARE @file VARCHAR(100)
Declare @dbid int
set @dbid=DB_ID('DBName') -- Change the DBName accordingly
SET @file=replace('C:\Backup\DBName_'+replace -- change the path accordingly here
(replace
(CONVERT(CHAR(19), GETDATE(), 120),':', '_'), '-', '_'),' ','_')+'.trn'
Declare @filesize int
Declare @maxsize int
Set @maxsize = 1024 --Add a value for which you need to restrict the Transaction logsize (in MB)
Select @filesize = size from sysaltfiles where dbid = @dbid and fileid =2
if @filesize &gt; @maxsize
begin
backup log  DBname to disk = @file with FORMAT, name=N'T_log backup'
Print 'Log Backup Taken Successfully '
DBCC Shrinkfile(2)
Print 'ShrinkDone'
end
end

All you need to do is replace DBName, backup path, and size with your desired values. Have you used such solution on your production server?

Reference: Pinal Dave (https://blog.sqlauthority.com)