Post reply

Maintenance Cleanup Task runs with wrong dates

  • December 15, 2006 at 8:44 am

    #114887

    I have a strange problem with a SQL 2005 maintenance task.

    As part of our overall backup strategy (SQL -> .BAK -> Tape), we often have conflicts between CommVault and SQL when SQL is trying to delete an older .BAK file while CommVault is copying to tape. This causes the Maintenance Plan backup job to fdail. To get around this, I have created multiple sub-plans, one to do the backups to disk, and a separate sub-plan to run the Maintenance Cleanup Tasks to delete the old BAK files. The Maintenance Cleanup Tasks are all configured to delete files older than 2 days, and if I click on the "View T-SQL" button, I see

    EXECUTE master.dbo.xp_delete_file 0,N'W:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\TestDB',N'BAK',N'2006-12-13T08:32:32'

    however when I run the job from SSMS, and review the output from the job, I see

    Cleanup Database Backup files

    Age: Older than 2 Weeks

    Task start: 12/15/2006 8:25 AM.

    Task end: 12/15/2006 8:25 AM.

    Success

    Command:EXECUTE master.dbo.xp_delete_file 0,N''W:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\TestDB,N''BAK'',N''12/01/2006 08:25:46''

    Running the job tries to delete files older than 2 weeks, not 2 days! With about 80GB of backup files / day on this server (and over 400GB / day on others, which are fortunately still on SQL 2000), we'd need over a TB to hold 14 days worth of backups!!

    Has anyone come across this, and have a solution?

     

    Thanks in advance ...

  • December 15, 2006 at 9:19 am

    #678378

    Hi Simon,

    I was just about to post a new thread when I saw your recent post.  Although i'm unable to answer your question I see you have problems which something i'm looking to implement.

    Briefly,

    What is the best way to have a maintenance plan delete old .bak / .log  files?  I'm used to 2000 Maintenance Plans where you can specifically tell sql to delete files older than so many days/weeks etc but how do you configure this within SSMS?  Can't seem to find anything in BOL although I must be looking in the wrong place 🙂

    Cheers,

    Mark

  • December 15, 2006 at 9:33 am

    #678384

    Mark,

     

    in SSMS, when you create a Maintenance Plan, you are creating an SSIS package (i'm not clear on whether it's a true SSIS package, or just an SSIS-like package, maybe someone else can add that piece of information). In the Maintenance Plan designer, you add the task you need, in the case of a SQL2000-like backup plan, you would add a 'Backup Database Task' and configure it for your requirements, then add a 'Maintenance Cleanup Task' to remove the old BAK files. You should also add a dependency between the two tasks, by clicking on the Backup task, and then dragging the green arrow that appears over to the Maintenance task.

    Hope that helps.

  • December 15, 2006 at 9:49 am

    #678391

    the simple way I did was create a small vb script, schedule it. It's easy and I never failed.

  • December 15, 2006 at 9:55 am

    #678394

    Thanks for the suggestion. I can delete the files easily enough, through a script or even SQL. I was looking to see if anyone else had come across and had a fix for this error.

  • December 18, 2006 at 3:20 am

    #678587

    Cheers Simon,

     

Viewing 6 posts - 1 through 5 (of 5 total)

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