0

I've noticed some unexpected behavior using the database backup job in Ola's Maintenance Solution. On the test instance I'm working on, I have the user database backup job scheduled to run at 2:00am every morning. I have the @CleanupTime parameter set to 48 and I haven't specified the @CleanupMode parameter.

If I'm understanding this correctly, that should run daily and keep two copies of the the database backup... Since the default is "AFTER_BACKUP", it should delete the oldest file after the new backup completes, only leaving the oldest file if the newest backup fails. So it should start with two days' of files in the folder, create a third file (for the current day) and then delete the oldest file of the three.

What's odd is that sometimes it leaves two days' worth of files and sometimes it leaves three, even though all of the backups are successfully completing. It's not a huge issue but, in the interest of saving space on that drive, I'd prefer to only keep two backups at any given time. Any tips or suggestions would be appreciated.

Edit: As requested in the comments below, here is the list of parameters in that job step...

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'ALL_DATABASES',
@Directory = NULL,
@BackupType = 'FULL',
@Verify = 'N',
@CleanupTime = 48,
@CheckSum = 'Y',
@LogToTable = 'Y'
  • Can you update your question to show the actual backup command parameters? Are you taking full only backups? – Scott Hodgin Jun 18 at 20:38
  • Hi Scott. Yep, these are full only backups and in most cases the databases are using the Simple recovery model. (Not sure if that info helps but figured it couldn't hurt...) – Jeff Edmunds Jun 18 at 21:00
  • 3
    It won't keep two copies, just as many copies that aren't 48 hours old. It's not about when your job starts, but when your job finishes. I still wouldn't expect this to leave three copies generally, unless one of them took a ridiculous amount of time to complete placing it within the 48 hour window. I suspect this is what is happening. – scsimon Jun 18 at 21:15
  • How long do your backups take? – Randi Vertongen Jun 18 at 21:15
  • Randi - these are mostly smallish databases. I think the entire job generally completes in about a minute. – Jeff Edmunds Jun 19 at 3:24
2

This can occur when you have variations in the runtime of your backups. If your backup job executes faster one day it may be less than 48 hours since the oldest file was created, so it will not remove it and leave you with 3 files.

Example:

  1. Backup1 created on 2019-06-17 at 21:01 - deletes backups created on or before 2019-06-15 21:01
  2. Backup2 created on 2019-06-17 at 21:03 - deletes backups created on or before 2019-06-16 21:03
  3. Backup3 created on 2019-06-17 at 20:59 - deletes backups created on or before 2019-06-17 20:59 which does not include Backup1

The easiest solution I've found for this is to subtract a few hours from the CleanupTime value. Setting to 40 or even 45 in the example above would mean Backup1 was deleted after Backup3 was taken.

  • HandyD - this makes sense. I’ll give it a try. Thanks!! – Jeff Edmunds Jun 19 at 3:25
  • This is what I do as well, offset the time interval by a couple of hours. – Tibor Karaszi Jun 19 at 6:47

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.