0

I have a Maintenance Plan that is suppose to go through the BACKUP folder and remove all .bak older than 5 days. When I run the job, it gives me a success message but older .bak files are still present.

I've tried the step at the following question: SQL Maintenance Cleanup Task 'Success' But not deleting files

Result is column IsDamaged = 0

I've verified with the following question and this is not my issue: Maintenance Cleanup Task(s) running 'successfully' but not deleting back up files

I've also tried deleting the Job and Maintenance Plan and recreating, but to no avail.

Any ideas?

Community
  • 1
Alex
  • 103
  • 1
  • 4

2 Answers2

1

I would recommend capturing the T-SQL call during the cleanup task to see if the input parameters make sense. For example - I captured the following call on version 10.0.2766:

EXECUTE master.dbo.xp_delete_file 0,N'c:\temp',N'.bak',N'2011-03-16T09:23:28',1

First five params in the previous example are type (1 for report, 0 for backup), folder, extension, date.

This will help identify if a particular parameter doesn't make sense (for example - extension fields being used are correct, dates not correct, errors not occurring). Also would be interested in knowing if that command works if executed outside of the context of the plan.

** Update ** I ran process monitor and filtered based on the SQL Server PID. I can't reproduce your error - but here is a screen shot of what a successful archiving should look like. Something for you to compare against - and also will let you know quickly if this is an underlying permissions issue:

enter image description here

Joe Sack
  • 321
  • 1
  • 5
  • EXECUTE master.dbo.xp_delete_file 0,N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup',N'bak',N'2011-03-11T12:30:41',1 – Alex Mar 16 '11 at 16:32
  • @Joe Clark **Could it be that the master.dbo.xp_delete_file is corrupt?** I had an issue with this SQL install which is now fixed, see: http://serverfault.com/questions/244869/ssms-2008-cant-add-maintenance-plans **Thank you for your help** – Alex Mar 16 '11 at 18:33
  • Just curious about the 'bak' versus '.bak' - let me test this and see if this was the issue. Regarding corruption - I would expect to see an error message, but may be possible (haven't seen that scenario before). Will ping you after testing your call. – Joe Sack Mar 16 '11 at 21:05
  • Okay - .bak versus bak didn't matter, I just tested it. – Joe Sack Mar 16 '11 at 21:07
  • And just to confirm - executing that command apart from the plan makes no difference, right? Doesn't work in either scenario? – Joe Sack Mar 16 '11 at 21:08
  • Okay - researching further and have the following questions: 1) Any issues related to xp_delete_file in the SQL Error Log? 2) Does the account executing the procedure have DELETE permission on the files in the backup folder? Another troubleshooting idea for you is to run process monitor while running the cleanup job and see if you're seeing anything unusual (like an Access Denied message). – Joe Sack Mar 16 '11 at 21:16
0

Another Serverfault user had the same issue recently, the below answer fixed the problem, give it a go.

SQL Maintenance Cleanup Task Working but Not Deleting

Community
  • 1
user75491
  • 1