Transaction log backup after full backup is too big

Our current production database performs a full backup daily at 7AM. The transaction log backup takes place every 15 minutes (24/7).

• Database version: Microsoft SQL Server 2014 – 12.0.4100.1 (X64)
• The .mdf file size: 3.9GB
• The .ldf file size: 6.5GB
• Recovery Model:Full
• Full backup size at 7AM: 3.8GB

The transaction log files (.trn) are normally below 50MB but after the full backup (at 7AM) the next transaction Log is around 1500MB. This is an issue for me because transaction log files are being transferred across network and restore time reason.

This is the script of the full backup:

BACKUP DATABASE [DB_PROD] TO
  DISK = N'F:SQLDB_PROD_backup_2020_10_12_114830_1849966.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'DB_PROD_backup_2020_10_12_114830_1849966', 
SKIP, REWIND, NOUNLOAD,  STATS = 10

There is a re-organize index step before the full backup. The command is like this:

ALTER INDEX [UC_LCRecipe_Name] 
ON [dbo].[LCRecipe] 
REORGANIZE WITH ( LOB_COMPACTION = ON )

My understanding is that full backups do not truncate transaction logs – only transaction log backups do (e.g., the full backup is fairly independent of the transaction logs).

However, my guess is that the re-organise all indexes does a lot of data processing – it is likely that these go into the transaction logs and are causing your issues.

You could test this by disabling or delaying the re-organise indexes one day, and see what happens to the transaction logs.

Have you measured the performance penalty for not defragmenting your indexes? It is a pretty big chance that you do all this work with little gain. See for instance http://sqlblog.karaszi.com/fragmentation-the-final-installment/.

And don’t confuse defrag with getting new statistics. If you see performance gains from a defrag job, it might just be because a REBUILD gave you new fresh high-quality statistics.

If you really need to defrag, first step is to only defrag what is really fragmented. There’s plenty written about this and pretty well established in the community so I assume you already have this in place.

Also, using BULK_LOGGED recovery model might be an option, but make sure you familiarize yourself with it and the (slightly (?)) increased risk when running in this mode.