Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem with Maintenance Plan Cleanup Task


Problem with Maintenance Plan Cleanup Task

Author
Message
Chris Stride
Chris Stride
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 187
Hi,

Sorry if this has been covered in other posts (I have been looking around) and I just haven't spotted the answer. I am having a problem using the Maintenance Plan Cleanup Task in SQL Server 2005 to delete backup files older than a certain number of hours.

I have backup files (all with a .BAK extension) residing on a separate disk (and logical drive) of a server. When the backups are performed, they go into folders on the root of this drive with names corresponding to the name of the database being backed up.

While we want to retain the most recent backups on the server for ease of restoration, they are fairly big files (even using a 3rd party compression tool), so I need to remove those older than a few hours for the next backups to be able to fit onto the disk.

After experiencing the problem of the plan executing without error, but not actually deleting the relevant files, I read that there was a problem with this with the initial release of SP2. I was on this version, so implemented CU7 to take the build to 9.00.3239.00. I have verified this version by running select @@version. The server was re-booted after applying CU7.

Prior to implementing CU7, I deleted the Maintenance Plan that contained the Cleanup Task that wasn't working. After implementing CU7 (and re-booting) I then created a new plan with just this Cleanup Task and set it to delete files on the F drive, with the 'Include first-level subfolders' checked, the folder set to the relevant logical drive and file extension set to 'BAK'. The file age was set to delete files older than 20 hours and the 'Delete files of the following type' is set to Backup Files.

The new plan executes without error, but the .BAK files older than 20 hours from job run time are still there. Does anyone have any ideas as to what I can check? or what I'm doing wrong?

I don't think there are any folder permission issues at play here (I even temporarily gave 'Everyone' full control!), so am a bit stuck. I really don't want to run a VB or whatever script to do this as I need things to be (very) easily supportable in my absence.

Thanks in advance for any help,
Chris
Animal Magic
Animal Magic
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1366 Visits: 13730
if you run the script manually does it delete the files?
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6678 Visits: 9914
The standard maintenance cleanup task will not remove files that are not native backup files. Since you are using a 3rd party backup utility (SQL Litespeed?), you need to use their tool to cleanup backup files, or roll your own utility.

Since you are trying to cleanup files that are a couple of hours old, I am guessing that these are either differential or transaction log backups. If so, are you making sure that these have been copied off somewhere else (tape, network share, etc...)?

If not, then you are going to have a problem with recovering the system in the event of a disaster. Before deleting any backup files, you definitely need to make sure you have a restorable copy of the backup file somewhere.

Jeff

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

jim.powers
jim.powers
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1580 Visits: 858
Jeffrey Williams (5/22/2008)
The standard maintenance cleanup task will not remove files that are not native backup files. Since you are using a 3rd party backup utility (SQL Litespeed?), you need to use their tool to cleanup backup files, or roll your own utility.


Since you are providing the file extension you want deleted, I don't understand why the cleanup task would not remove files outside of the native backup. My understanding is that it compares the date/time on the file plus the extension to determine what to delete, not the contents.
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6678 Visits: 9914
I am not sure if it checks the contents or uses information in the backupset table in msdb - however, it will not cleanup non SQL Server created files.

You can easily test this yourself. Create a native backup (or copy an existing backup file to another directory). Create another file with a similar name (change the date stamp) using notepad, create a new maintenance plan to clean up this test directory. The only file that will be removed is the native backup file.

Jeff

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

MANU-J.
MANU-J.
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2490 Visits: 8766
Chris,

Have you specified any file extension in extensions box? Try specifying *.* and see if it deletes the old files now.

Manu
Chris Stride
Chris Stride
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 187
Dear All,

Thank you very much for your replies. Much appreciated. Yes, it is SQL Litespeed I am using as the 3rd party compression tool. I didn't realise that the SS2005 Maintenance Cleanup Task only removes native SQL Server backups. I just assumed that it would delete files in the location and with the extension specified, but I guess the radio option 'Delete files of the following type' should have been a clue.

They are full backup files of a Data Warehouse which we take both pre and post nightly data load from the various operational systems that feed into it. The disk on the server the files backup to isn't particularly big and can only hold the most recent pre and post data load backups at any one time. Note that these backups are further backed up to an offsite location, hence it's OK to delete them like this. In any event, the operational load could just be re-run if for some reason no up to date backups are available.

Interesting. Annoying, but interesting. I will see what Litespeed offers to do this. I hope that can deal with hours as the backups I will be wanting to delete each time are less than 24 hours old.

Thanks once again,
Chris
Chris Stride
Chris Stride
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 187
P.S: Hi Manu,

I had 'BAK' in the extension box. I also tried both '*.*' and '*', but it made no difference. I guess that makes complete sense if it won't clean up native backups anyway. Thanks though.
tosscrosby
  tosscrosby
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3028 Visits: 5316
If you have a pre and post backup, couldn't you simply init during each backup (or am I missing something)? That would overlay the latest of each of these backups, leaving two on disk at any given time.

-- You can't be late until you show up.
MANU-J.
MANU-J.
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2490 Visits: 8766
You are right Chris as it won't delete any backups apart from SQL native ones.

Manu
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search