Deleting of old backup files in a Maintenance plan

hey guys...

as you probably know, theres an option in those maintenance plans (I guess thats the name in english) that allows you to delete previous backup files after having successfully managed to build the current one ....... automatically ..

It is happening to me now and again that the ServerAgent doesnt seem to be working fine (hope is not because of the alcohol at night), but it doesnt manage to delete those files from the server, with the fuss of having to be aware -the lazy DBA- of deleting them manually every "now and again" too ...

Anybody has any clue to gimme?? I have tried to change users running the service, checked out the permissions on the server and have taken many other obvious actions ... but for any reason, sometimes it just happens ....

thanks a lot ...

Theoretically, the BackupExec (Veritas?) we are using just gathers the files generated by SQL ...

we have been studying the timings and, in theory, they act against the disk at rather different times during the night ....

Are you getting any errors on this? If so, can you post the error message? Where are you at in Spain? <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.

We have the same issue, sometimes sql server will not rotate the files. Most likely due to our third party backup software still holding some file locks but I haven't had time to verify this.

Actually, two or three points ::

I have disabled the Veritas software to access the SQL directly, so its just taking the built-up files already packed by the maintenance plan. Veritas and the SQL plans werent working fine together ... so, right now is just a question of FILES being accessed, not actual databases.

On the other hand, I am not being reported any error. The plan just seems to be working fine untill you realize IT IS NOT doing so ... just the other day one of the servers collapsed due to disk problems ... silly me!

Satya, (havent read the referred paper yet) I will try to set up different maintenance plans and will tell u the results if any noticeable report. We've got like 40 databases (I'm not at the site right now) and the sizes vary from 2GB to just a few Mb's... we have service pack 3a with a cluster Windows 2003 ... but this is not really important as it is happening as well in some other smaller servers with w2000.

Argyle, thanks for the comprehension, I thought I was alone int those issues.

Hey Derrick, I am from Barcelona, actually a part of Spain and the most interesting place over here ... no doubt!

I think if one maintenance plan is having problems rotating the files, the rogue locks (from veritas or from wherever they may be) will still impact the files when the maintenance plan is split out into smaller jobs.

I think the option I would choose is to write a stored proc to scan the directory and delete the files using xp_cmdshell.
Assuming you have plenty of free space on your backup drive, it is usually not a problem for a backup to lay around for 2 or 3 days, often the problem is that lots of these are building up and never being deleted.
A job of this kind should solve it because it will keep trying every day until it can delete it.

Maybe ill write such a job if youre struggling.

Hey guys,
it appears to me that whatever I do (split out the maintenance plan "MP" in two, for instance) doesnt end up solving the problem ...

the referred articlehttp://support.microsoft.com/default.aspx?scid=kb;en-us;288577 only gives you some guidelines as how to detect and proceed in case of MP failures ... nothing related to any bug or fixing ...

I am VERY RELUCTANT to write any code -lazy DBA, I'd insist- ... I could do so, but I just want a Microsoft product to be working as it is supposed to be doing!! grrrrrrrr!

Chappy, the problem is not having more than one file (actually I keep up to 3 files/database), the real issue is not being noticed about the disk space shrinking because of too much files around ......

Welcome to the "not expected to be here backup files" party, held on your server hard disks every night after the MP!!!

Wabbash, go into your maintenance plan jobs, select the job step, and write the results to an output file. See if you can capture any errors from that. Sometimes, this will capture errors the maintenance plans do not, which isn't saying much. They pretty much SUCK. <img src='/community/emoticons/emotion-1.gif' alt='' /> We wrote our own log shipping process and it works much better than the stupid MS product. You might want to check out alternative solutions. I think Tara has some scripts for this at<a target="_blank" href=http://weblogs.sqlteam.com/tarad>http://weblogs.sqlteam.com/tarad</a> .<br /><br />I love Barcelona. I also love Madrid and Sevilla though. <img src='/community/emoticons/emotion-1.gif' alt='' /> I think it's a toss up on parties, history, and beauty though between Madrid and Barcelona. I'd happily live in either city. (this should get a response) lol<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.

Hey Mean Old DBA....... I have read the RAMBLINGS of a DBA (by TARA) quite nice link ...!
(athttp://weblogs.sqlteam.com/tarad) I really encourage anyone to have a look there.

Will try up (?) that script, in fact IT IS SOMETHING I SHOLD HAVE MANAGED by myself (paths, dates, access to disk...) but I was reluctant to implement (due to my built-up FAITH on unreliable MS products .... that sucks, but its me -always have thought we purchase products for some reason, right??)-. To all of you, the "backup script" written there sounds great, we should try to develop other scripts that would behave as the maintenance plans key features do ... (checking integrity, repairing databases...) I think Tara has developed some of them as well ...


As for my "country", lets say Sevilla / Madrid / Barcelona are all located in the same region (Spain) but they have barely nothing in common. Barcelona is the most different of them all, as -in fact- is the capital of another country (Catalonia) and we speak a different language (say "navajo" all around, though spaniards say we speak "polish" .. LOL)

Derrick, did u finally go to Venezuela? (sorry readers, that is rather personal) ... I read you blog http://weblogs.sqlteam.com/derrickl) and it sounded that u were about to go there in June (that WAS in June...) ... Anyway, if you havent headed to there yet, ask me for info, I was there in April ...

ta-ta for now, (this should get a response, try to catch me up at www.wabbash.com)

Fraankkkkkkkkkk!!!!! my goodness, what did you do in MARBELLA and MALAGA????

no waaay, thats the crappiest and ugliest place in Andalucía, rather say, the whole country!!

Should u come to Barcelona, dont hesitate to drop me a line or gimme a phone call and I could manage to show you around, for the sake of the community ... (hey DBA's, I encourage to come over too)

Which is the quote u are talking about the Rainer-Maria Rilke one...?¿¿? yes man, stunning poet ... He probably didnt know what a computer was but .....

I'll take you up on that also. <img src='/community/emoticons/emotion-1.gif' alt='' /> I went backpacking outside of Barcelona and stayed there for a few days. Then, I spent a week in Madrid. I started the trip though with 5 weeks in Sevilla, traveling on day and weekend trips all over Andalucía and part of Africa. Spain is a beautiful country. The museums, history, and architecture in Spain are incredible.<br /><br />The Spanish government still seems to have a bit of a problem seeing Catalonia and the Basque region as different countries. lol The whole autonomy thing is interesting though to say the least.<br /><br />I didn't go to Venezuela. I had some things come up. I'm planning on going later this year. Any ideas would be appreciated. Hopefully, our governments will be getting along a little better by then.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.

Derrick, you are welcome too ... of course.

It looks like you are quite aware about (of?) the catalonian issue .... nice to know it! sometimes USA citizen are regarded as dumbers-dumbs ignorers of the off-world out of the States .... glad to know you dont belong to such genre ...

So u are a backpacker? welcome to the club! is it that all DBA's end up touring the world with a backpack and a shoestring ... being a member of this club (www.hospitalityclub.org) I usually host Nice-to-be backpackers ... so, feel free to take my invitation under consideration, should u come up to Europe ...

Recommendations for Venezuela?? many! visit my photo gallery and probably u can have a hint or get some inspiration ......http://www.wabbash.com/travels/VZL2004/VZL2004_1.html

salut!

(this is ONLY related to SELF_SPIRIT_MAINTENANCE_PLAN)

Come on Frankkkkkkkkk I have seeen the Hotel you were in ANDALUSIA and that has nothing to do with a backpackers standard dormitory!!!<br /><br />It has a grass ground for playing tennis, in the driest place of Europe!!<br /><br />Kidding man, Derrick, apologize for your 5-week trip, it is not fair, and u know it <img src='/community/emoticons/emotion-4.gif' alt='' />, next time we will give you a 2-weeks stick-in-town in Marbella as a punishment .......<br />

Sorry to spoil your travelling talks guys... sounds very interesting. <img src='/community/emoticons/emotion-6.gif' alt='' /><br /><br />I was just planning to post this entry, when I came accross your active one:<br />"<br />I know I know.... it is not a good practise to use Maintenance Plan's for taking backups. But, I inherited an huge SQL environment and I am still putting out fires, so have not gotten around to implementing "best practices". <br /><br />The last week or so, I've had backup jobs failing due to disk space shortage. Then after investigating, I then noticed that the previous day/s backups are still there, although the 'remove files older than' option is set to 1 day. The previous day/s job/s were successful, it is only due to the diskspace shortage that the jobs eventually fails. In all the cases (on more than one server) that I've had, the backups files are written to local drives. Any suggestions? <br />"<br /><br />But why oh why will this demon now suddenly rear it's ugly head if we've been getting away with it for or years?

No ways, thanks for getting us down to earth !

The most enthralling thing about MP's (beware, dont confuse with any Microsoft Product, in fact it is) is that after failing, the processes sometimes blocked some of the databases to Single-user ....

then, u arrived at work happily in the morning and found some UGLY and ANNOYED faces staring at you asking for EXPLANATIONS .....

everything due to diskspace shortage ....

Will try to write the key pitfalls of MP's later (I dont know if I will succeed), just gathering the info ... so, probably i will have to open a new topic......

I would at a minimum create a "backup job" that does nothing but look for old files and delete them. It's not that hard if you're putting the datetime on the filenames.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

I must admit that I sort of use maintenance plans, or more to the point I have a maintenance plan which does nothing except say which databases it is for and then create a job to run the xp_sqlmaint procedure against that plan specifying the command line options that I want... I haven't had any problem thusfar with this...? Are people here saying that they do often have problem with using xp_sqlmaint?

Cheers
Twan

in sql server 2000 maintenance plans how do you create a cleanup only plan? I can not select "delete files older than" unless i specify a database and tell it to back it up.