December 12, 2007 at 10:35 am
Hi
I have a maintainence cleanup task to cleanup bak files older than a day and it does not seem to be working and its filling up our harddrives.
is this a bug in sql 2005? we are on sp2.
Is there a workaround for this problem.
Thanks
Sanra
December 12, 2007 at 10:14 pm
What is the filename extention of your backup files? Manitenance plan cleanup task deletes only .BAK/.TRN files.
Workaround is use FORFILES.EXE. Create a .BAT file and schedule it.
December 13, 2007 at 6:25 pm
sanra001 (12/12/2007)
I have a maintainence cleanup task to cleanup bak files older than a day and it does not seem to be working and its filling up our harddrives.
Are the older backups on the same backup set or is it a whole separate file? If it's in the same file you could set the @expires (or something like that) to a certain date.
If your backups are separate files, you could write an SSIS package to delete those files older than x days.
December 14, 2007 at 10:20 am
Which release of service pack 2 do you have? If you have the initial release, there is a bug http://support.microsoft.com/kb/933508.
The bug basically adds the next date increment to the criteria. For example, if you say delete after 1 hour, the plan deletes after 1 day, and if you delete after 1 day, the plan deletes after 1 week.
You will probably have to go to cumlative patch for sp2.
February 5, 2008 at 4:09 pm
I had the same or very similar issue:
In a vanilla maintenance plan:
1. backup database
2. delete backups > x days old
The maintenance plan history showed all 'good'. BUT... old backups were not deleted. No errors reported anywhere...
Issue was that the service account was not accounted for in the backup directory folder - at it explicitly with 'modify' or 'full control' permissions. Fixed up the issue nicely. Nice bug...err feature. MS made my company pay $$ for the support call on this one. 6 hours worth...
February 5, 2008 at 4:53 pm
Delete the maintenance plan and recreate it. If it works correctly, it was due to the bug in the service pack. I no longer have the link, but MS said the solution was to recreate the plan.
-SQLBill
February 5, 2008 at 6:41 pm
Critical Update for SQL Server 2005 Service Pack 2:
Download and apply to your SQL2k5 client tools (your own desktop/workstation).
I had this problem before. It works after this update.
February 6, 2008 at 5:04 am
where it asks for the file extension make sure you put just 'bak' and not '.bak'
---------------------------------------------------------------------
February 7, 2008 at 8:52 am
If you used the maintenance wizard to create the backup, I have occasionally experienced a problem where the maintenance wizard doesn't record the # or measurement of deletion. The fix is to manually open and edit the script to include the 1 DAY attribute. If you think this is related and need more help, let me know and I will post more explicit directions.
-M
February 8, 2008 at 7:53 am
Hello,
I have come across this kind of issue many times before but primarily in SQL 2000; as a consultant I visit different locations almost daily.
With SQL 2000 I found that sometimes the Maintenance Plans worked fine for a period of time, perhaps months or longer and then for apparently no explicable reason - the disk dumps stopped deleting.
I found the most common cause at my clients was running the jobs manually for whatever reason - perhaps they wanted a quick backup before doing something, thus they manually kicked off the job.
Seemed that sometimes this through the system for a loop and next thing backups no longer got removed. - to fix this I manually cleaned up history etc and moved/deleted the old backups
One SQL 2005 I have seen it happen also sometimes for the same odd reason and sometimes due to the way the job was set up.
I had one server that had the maintenance plan set up as: DBCC - Backup - Delete old backups - delete history - in that order - for some bizaar reason files never got erased - so I moved the History/Maintenance clean up to a subplan - and it worked.
Also - if you have SP2 make sure you get the last build or the updates to it as there where numerous versions....
Anyway thats my half-penny worth 🙂
Cheers
Ian
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply