2

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.

Execution Log

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.

  • Are you sure the full backup was completed before 6:30 AM? – Aaron Bertrand Oct 23 '15 at 18:01
  • @AaronBertrand forgot to include the screenshot. Take a look. The full index optimize, DB check and full backup takes 37' – Diego Jancic Oct 23 '15 at 18:04
  • @PJMahoney I've added more info. Process-CT-Cube does not work in that database. – Diego Jancic Oct 23 '15 at 19:26
  • 1
    Wouldn't the LOG backup right after the IndexOptimize / Database Integrity Check / Full Backup contain the activity of at least the IndexOptimize step? Not sure if any log activity occurs during Database Integrity Check. Plus any other changes happening between 4:30 and 6:30. – Solomon Rutzky Oct 23 '15 at 20:14
  • 1
    Just note SQL Server defer's transaction log backups while the actual full backup is taking place but I'm not sure if that would have a major effect. I think if you do a checkpoint and log backup after reindexing/dbcc you will catch the growth most likely. Look at the time taken and size for both operations. If they are on the same/similar subsystem that correlates to the size of the backups, as long as both have compression enabled/disabled. – Ali Razeghi Oct 23 '15 at 21:10
3

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).

  • Thanks. If that's the case (I'm going to test it during the week). What would be the solution to be able to do a IndexOptimize and now having the log backup be huge? I'm running LOG backups from 6:30am until 4:30am next day to avoid it running at the same time the FULL backup runs. I'd appreciate if you can extend your answer and tell me how to configure/schedule it right. Thanks. – Diego Jancic Oct 23 '15 at 20:40
  • @DiegoJancic I did add a suggestion: add a LOG backup between the "IndexOptimize" and "Database Integrity Check" steps. If all of the LOG backup size is from "IndexOptimize", then maybe try to schedule a LOG backup for 4:36 AM to attempt to cut the resulting LOG backup in approximately half. – Solomon Rutzky Oct 23 '15 at 20:44
  • Got it thanks. I was wondering if it's possible to avoid having those huge LOG backups. Doing a LOG backup after the Index optimize would not avoid that but it would speed up restores since that last LOG will be most of the time not needed. – Diego Jancic Oct 23 '15 at 20:54
  • Might not be "needed" from a "state of the data" point of view, but I believe you are required to restore it so that you can have an unbroken chain between FULL / DIFF backup and the most recent LOG backup. As I added to my answer, try scheduling one or even two LOG backups during the IndexOptimize step. Maybe even do every 2 minutes to see if that works. – Solomon Rutzky Oct 23 '15 at 20:59
  • 1
    Thank you all. That was the problem. I've changed the Index Optimize to execute once a week only and with other parameters as described here: brentozar.com/archive/2014/12/… Now the LOG took around 100mb the first day after change. – Diego Jancic Oct 27 '15 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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