Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Problem with Maintenance Plan Cleanup Task Expand / Collapse
Author
Message
Posted Thursday, May 22, 2008 10:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 8:14 AM
Points: 23, Visits: 136
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
Post #505309
Posted Thursday, May 22, 2008 10:22 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:29 PM
Points: 990, Visits: 13,439
if you run the script manually does it delete the files?
Post #505312
Posted Thursday, May 22, 2008 11:55 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 4:08 PM
Points: 4,379, Visits: 9,469
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #505378
Posted Friday, May 23, 2008 8:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 21, 2013 2:59 PM
Points: 1,329, Visits: 807
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.
Post #505891
Posted Friday, May 23, 2008 11:21 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 4:08 PM
Points: 4,379, Visits: 9,469
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #506037
Posted Friday, May 23, 2008 12:58 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, February 27, 2014 11:17 AM
Points: 1,688, Visits: 8,756
Chris,

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

Manu
Post #506111
Posted Tuesday, May 27, 2008 6:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 8:14 AM
Points: 23, Visits: 136
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
Post #506883
Posted Tuesday, May 27, 2008 6:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 8:14 AM
Points: 23, Visits: 136
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.
Post #506891
Posted Tuesday, May 27, 2008 7:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,832, Visits: 5,316
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.
Post #506926
Posted Tuesday, May 27, 2008 9:23 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, February 27, 2014 11:17 AM
Points: 1,688, Visits: 8,756
You are right Chris as it won't delete any backups apart from SQL native ones.

Manu
Post #507002
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse