Database Maintenance- Cleanup Task

Question

Database Maintenance- Cleanup Task

discover.sql

Mr or Mrs. 500

Points: 542
More actions
August 5, 2009 at 3:00 pm

#207815

Hello,
I have a database cleanup maintenance task set for deleting .bak files after 3 days. I just used the switches to set it. But, the strange thing is, it works fine with all files except one. It stores 3 days for all the files, but for one of them, it stores 4 days. I'm not sure why it does this. Similarly the task for deleting .trn, I have set it up to delete files after 1 day. It is doing it properly for all but one. this one is a different database than the one whose file is behaving strange for .bak
Please let me know if i can resolve it somehow
thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 1

If you are using the standard maintenance cleanup task - this can happen because it works based upon the time the task runs. If the time frame you are using is anything older than 3 days - and the job takes less time to run today, the file 3 days ago will not qualify.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

JamieX Hall of Fame Points: 3763 More actions · Answer 2

Change it to use hours.

Delete files older than 78 hours (3 days + 6 hours). You will get a more consistent retention.

discover.sql Mr or Mrs. 500 Points: 542 More actions · Answer 3

Hi Jamie

Thanks for your reply. Sorry for being so dumb but why 72+6 ?

JamieX Hall of Fame Points: 3763 More actions · Answer 4

add on a few hours to compensate for the variance in backup time.

it doesn't have to be 6 hours. depends on how long your backup tasks run over all your databases. If your jobs take longer than 6 hours, add more.

From what Jeff said earlier, if it runs earlier, the older backup may not qualify to delete so by adding a couple hours, it will always qualify

discover.sql Mr or Mrs. 500 Points: 542 More actions · Answer 5

Hi,

Why would it not qualify if the job runs for a short time ? How do I check how long the job took to run ? Sorry for asking such questions

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 6

Let's say you have the following tasks in your maintenance plan:

Check Database Integrity

Backup Databases

Maintenance Cleanup

And now, let's say that normally the integrity check takes 1 minute and the backup takes 1 minute. You start the job at 1am. The maintenance cleanup task therefore normally starts at 1:02am and will delete everything that is more than 3 days old. That is, any file with a last modified date of 3 days ago less than 1:02am.

So, if 3 days ago - the backup process only took 30 seconds, the last modified time of the backup file would be less than 1:02am. If 3 days ago - the backup process to 1:30 - the last modified time on the backup file would not qualify.

Now, let's take it a step further and say that today our backup was hung up for some reason and took twice as long. Now, the maintenance cleanup task is going to remove files older than 3 days ago at 1:03am instead.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

discover.sql Mr or Mrs. 500 Points: 542 More actions · Answer 7

Thanks Jeffrey for such a detailed explanantion. I got it now. 🙂

One last thing. I would like to know the exact duration of my backups or how long the job runs for

Where can I see that?

Thanks

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 8

Right-click on the maintenance plan under the maintenance folder and select View History. That will show you how long each task takes.

Now, if you are executing the task for multiple databases - this will not show you how long it took for each database. If you want to see that, then look in the SQL Server Logs - which will show you when each backup started.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

discover.sql Mr or Mrs. 500 Points: 542 More actions · Answer 9

Hi,

When i changed it to hours, It does not delete files older than that. For example. I set the files to get deleted after 51 hours. I wanted only 2 day's of files to remain but it still stores 3 days of files.

Am i missing something?

Thanks

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 10

What are the actual date/times of the files - what time did the task start?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs