Post reply

Database Maintenance- Cleanup Task

  • 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

  • August 5, 2009 at 5:00 pm

    #1035135

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • August 6, 2009 at 9:20 am

    #1035453

    Change it to use hours.

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

  • August 6, 2009 at 9:23 am

    #1035454

    Hi Jamie

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

  • August 6, 2009 at 9:30 am

    #1035468

    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

  • August 6, 2009 at 1:06 pm

    #1035625

    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

  • August 6, 2009 at 1:47 pm

    #1035649

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • August 6, 2009 at 2:57 pm

    #1035682

    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

  • August 6, 2009 at 4:09 pm

    #1035717

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • August 7, 2009 at 1:44 pm

    #1036275

    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

  • August 7, 2009 at 2:15 pm

    #1036287

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

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

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

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