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:\SQL\DB_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 )