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
- A transaction log grows unexpectedly or becomes full in SQL Server
- Troubleshoot a Full Transaction Log
- Recover from a full transaction log in a SQL Server
My few earlier blogs on the same topic can be found below,
- SQL SERVER – Huge Transaction Log (LDF) for Database in Availability Group
- SQL SERVER – How to Stop Growing Log File Too Big
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 > @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)
