Maintenance Cleanup Task runs with wrong dates

Question

Maintenance Cleanup Task runs with wrong dates

Simon Facer

SSCertifiable

Points: 5427
More actions
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 ...

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

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

Shabba SSCertifiable Points: 5007 More actions · Answer 1

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

Simon Facer SSCertifiable Points: 5427 More actions · Answer 2

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.

T Y MA SSC Veteran Points: 271 More actions · Answer 3

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

Simon Facer SSCertifiable Points: 5427 More actions · Answer 4

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.

Shabba SSCertifiable Points: 5007 More actions · Answer 5

Shabba

SSCertifiable

Points: 5007

December 18, 2006 at 3:20 am

#678587

Cheers Simon,