First log backup after FULL taking lots of space

Question

In the backups I'm doing in my database, the first LOG backup I do after the FULL backup is taking too much space, even more than the full backup.

For example:

4:50 AM:  FULL BACKUP  - .bak 7.2GB
6:30 AM:  LOG BACKUP   - .trn 9.3GB
7:00 AM:  LOG BACKUP   - .trn 2mb
7:30 AM:  LOG BACKUP   - .trn 150mb
8:00 AM:  LOG BACKUP   - .trn 1mb
8:30 AM:  LOG BACKUP   - .trn 50mb

Note that the first LOG backup is in GB, while the others are in MB.

What I'm doing is the following:

• 4:50 am: (using multiple steps in the same job, not running all of them in parallel)
  1. Index optimize
  2. DB integrity check
  3. FULL backup
• 6:30am and every 30 minutes after that:
  • LOG backup

Also, another job called PurgeOldImportData is executed every hour and it deletes old information, but it's currently deleting very little information (if any). In fact, it takes only 2 seconds to execute.

Can anyone tell me why is this happening? Besides the space it takes, it takes longer to do the RESTORE. Thank you!

UPDATE:

I'm using Ola Hallengren's scripts for backup. This is what each job/step does:

JOB: DatabaseBackup - FULL

1. IndexOptimize: [dbo].[IndexOptimize] @Databases = 'USER_DATABASES'
2. Database Integrity Check: [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES'
3. Full Backup: [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'D:\SQLServer\Backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 24, @CheckSum = 'Y'
4. BackupToS3: (executes a .bat which moves the file, does not touch the SQL Server)

JOB: DatabaseBackup - LOG

1. [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'D:\SQLServer\Backups', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 24, @CheckSum = 'Y'
2. BackupToS3: (executes a .bat which moves the file, does not touch the SQL Server)

At night users run some import data files, but they are not running nothing that produces 9GB of changes AFAIK. Even more, the biggest data imports are not run from 4:50am to 6:30am.

Answer 1

Taking a FULL backup won't clear out the transaction log entries (unless the database is in SIMPLE Recovery Model). So whatever changes take place between your last LOG backup before the FULL backup (@ 4:30 AM), and the first LOG backup once that process is over (@ 6:30 AM) would be included in that 6:30 AM LOG backup. That is a 2 hour span (you typically do a LOG backup every 30 minutes otherwise) in which you, at the very least, run index maintenance via the IndexOptimize stored procedure.

If you want to test this out, just temporarily disable both the "IndexOptimize" and "Database Integrity Check" steps (meaning, if you are using the CmdExec step type so it can call SQLCMD, just add REM to the beginning of the line in both steps). Let that run for one night and if I am right, it will be very obvious. Then, remove the REM from one of the two steps so that only one of them runs again (maybe start with the "Database Integrity Check" step) and let that run for one night and then you should see how much impact each of those first two steps has on that "first" LOG backup.

If the "Database Integrity Check" step is taking up more than a small portion of that LOG backup, then you might be able to split that initial backup into two (even if not an even 50/50) by doing a LOG backup after the "IndexOptimize" step. If all (or most) of the LOG backup size is from the "IndexOptimize" step, then maybe try to schedule a LOG backup for 4:36 AM to attempt to cut the resulting LOG backup in approximately half (given that you show that first step running for just under 14 minutes).