0

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 )
Improve this question
0

2 Answers 2

Reset to default
1

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.

Improve this answer
2
  • you are correct 100%, I have disabled the reorganize index step and then tried to run the full backup, and the next transaction log file is only 3MB. However, the DB needs to be scheduled to reorganize and performs the transaction log backup automatically. So how can I achieve both? It means that the DB keeps reorganizing index as usual while the transaction log files are still small. Thank you very much Peter
    – Peter Nguy Nguyen
    Oct 12, 2020 at 2:54
  • 1
    The INDEX maintenance has to log into the transaction log as pages are being changed and so the databases needs to keep track of these transactions to remain transactionally consistent. If it it that much of an impact there are 2 possibilities: (1) your indexes are not appropriate as they are becoming quite fragmented and there is a lot of work to do to fix them. In this case you need to review your indexes and possibly make changes so they become less fragmented or (2) spread the index maintenance over a number of batches so that not all of it is included in a single transaction log backup.
    – Martin Cairney
    Oct 12, 2020 at 4:26
1

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.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.