Maint Plan not deleting old Backup Files
-
I have a SQL 2005 maint Plan, and I put in a step to delete backups older than 2 days. It sure looks right, but the old files are not getting deleted... The hard drive is filling up. Here's the portion of the log that shows that the step completed normally:
============
Maintenance Cleanup Task (SQLTESTSERVER)
Maintenance Cleanup on SQLTESTSERVER
Cleanup Database Backup files
Age: Older than 2 Days
Task start: 3/19/2006 7:00 PM.
Task end: 3/19/2006 7:00 PM.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''F:\Volumes\Backup\DataBaseName\'',N''.bak'',N''03/17/2006 19:00:07''
============
What am I missing ??
-
Maint. plan in 2005 is not upto the mark and is having few bugs, that need proper fix. Hopefully MS will come out with that in near future.
I have raised this and few other issues related to maint. plan here and at various form, but from microsoft or form member or mvp's, I have still not rec. any convincing answer, except it is better to not use MPlan right now.
Here is the link to my previous post.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=241231&edit=1
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com -
Instead of using 2 days
try using hours - for example 40 hours
-
Thanks, but the only units of time are: Days, weeks, months & years. Hours should be there though
-
The problem is in your statement below. Remove the .bak and replace with bak
It only needs the three character extension.
Command:EXECUTE master.dbo.xp_delete_file 0,N''F:\Volumes\Backup\DataBaseName\'',N''.bak'',N''03/17/2006 19:00:07''
-
Hello
Instead of using 'Maintenance Cleanup Task', use 'Execute T-SQL Statement Task' and key in the following SQL commands. This should fix the error. This is an work around.
---------------------------------------------
declare @dt datetime
select @dt=getdate()-1 -- 1 is the files with 1 day old
EXECUTE master.dbo.xp_delete_file 0,N'D:\DumpDev',N'BAK',@dt
-------------------------------------------------
Regards
N.Raja
-
THANKS GANG!
One of my servers was bloated because of this issue. Same thing happened to me even using the SQL statement instead of the clean up task did not work. That "dot bak" bug is nasty thanks a million for the tip!
Skål - jh
-
I am disappointed to say the least on the redo of the maint. plan stuff in 2005. It was 95% very good in 2000. They have removed so much flexability in 2005.
-
I must agree, the more we live with it the more flaky it seems.
Am now struggling with the tansaction log backup. It does not seem to be truncating the logs and there is no option in the SSIS task!
Am having serious full log issues now. Didn't want to leave the databases at unlimited growth, is that the only option? Setting the max log size in the DB properties and creating a transaction log back up job, which I expected would truncate the log, has caused the ETL jobs to fail because the target DB log is full.
Any one have a good way with dealing with log bloat and forcing the back up to truncate?
thanks again gang!
Skål - jh
-
I would open a case with MSFT. The product 'should' work.....
Alternatives are to create the backup statement yourself and create a job to run it the old fashioned way... OR buy an alternative software companies backup/restore product !
-
Thank you Markus.
Well for now I am returning the DBs to unlimited growth for the logs.
Maybe I've just forgotten, but I don't recall having to do this in SQL 2000. Setting up tans log backups seemed to take care of things. The logs seemed to truncate with the nightly backups and I didn't hit full log problems.
There must be additional nuances as well as bugs in 2005 I need to learn about.
thanks a million!
Skål - jh
-
Functionality I think it is the same. I think there is something not working correctly would be my guess. I have installed SQL 2005 and played around with maint. plans but do not have anything live in production yet so I cannot test any theories about trans. logs.
Good luck. If you find anything out please post your findings so we can all learn from it.
-
in the maintenance plan task for delete old bakcup file: if you specifiy "include subfolder" (sp1 only) you must add a "\" after the path specification, and always remember that you must change the default ERRONEUS extension specification *.bak or *.trn to bak and trn respectively.
This the unique way I know to get this task working... but... in MS who write the code dor maintenance plan ? Did he know stored procedure parameters ?
"...e il mio maestro mi insegnò com'è difficile trovare l'alba dentro l'imbrunire" F.Battiato - Prospettiva Nevsky -
grazie Marco,
Those are very good tips to know, thank you for posting them.
As of now the script version of the clean up task seems to be working well.
For the DB logs these steps seem to be managing the log file size:
- detached and reattached the data bases without the log file so a new empty one was created
- set affected databases to simple recovery (fortunately for now I can, as all data is dynamically imported)
- set a limit on the log file growth
- added transaction log backups to the nightly maintenance plan/job
Growing forward the DBA team is very inclined to abandon the SSIS based maintenance plans and simply script everything out. It's a harsh comment I think when 3 DBAs recommend we avoid the latest 2005 features because of both perceived and actual unreliability.
Skål - jh
-
I am having trouble with this.
I am so unimpressed with this incarnation of SQL.
Yet again it seems to be a case of taking a perfectly good system and a*sing it up totally.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply