May 22, 2008 at 10:17 am
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
May 22, 2008 at 10:22 am
if you run the script manually does it delete the files?
May 22, 2008 at 11:55 am
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 23, 2008 at 8:21 am
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.
May 23, 2008 at 11:21 am
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 23, 2008 at 12:58 pm
Chris,
Have you specified any file extension in extensions box? Try specifying *.* and see if it deletes the old files now.
Manu
May 27, 2008 at 6:44 am
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
May 27, 2008 at 6:52 am
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.
May 27, 2008 at 7:39 am
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.
May 27, 2008 at 9:23 am
You are right Chris as it won't delete any backups apart from SQL native ones.
Manu
September 30, 2008 at 3:43 am
Hi!
I got excited when I found this topic because Grasshopper describes my problem exactly - I'm backing data up to Drive E:\backups, I've told it to remove files older than 24 hours, but they just won't go.
But in my case I do NOT use SQL Litespeed - it's just vanilla SQL Server.
One possibility is that the server is running SQL Server 2000 - might there be some niggly problem between this and 2005?
Regards
Jack
September 30, 2008 at 11:32 am
Is agent account having enough privileges on backup folder namely Read/Write.
MJ
October 1, 2008 at 2:27 am
It has enough privileges to create last night's backup!
But it isn't deleting the older backups
:o(
October 1, 2008 at 6:07 am
Have you tried running the code from query analyzer? Does it delete the files when ran from query analyzer?
MJ
October 1, 2008 at 7:15 am
Hi Manu
I'm running SQL Server 2005, so no (Query Analyzer is a 2000 thing, I think) .
The job is set up as a legacy maintenance plan (originally created in SQL Server 2000, before my time) and then run by SQL Server Agent nightly. It makes the backups, but doesn't delete the older backups as specified in the plan
I tried setting the maintenance plan running independently of SQL Server agent - same result - it takes the backup but fails to delete those files older than 48 hours as specified in the plan
Thank you for your interest!
Reagrds
Jack
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply