SQL Server Performance Forum – Threads Archive

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 …

Do you use any Anti-virus software or third party backup tool for backups apart from usual DB maintenance plan? Refer thru this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;288577 to troubleshoot Db maint. plans. And also check maint. plan log for information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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 ….

As a test better disable the Veritas tool to ensure no other application is accessing the SQL backup files. Also try to design a new plan for one database and schedule it in order to ensure no issues with the plan execution. BTW how many databases are involved and what are their sizes|? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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=’

‘ /> MeanOldDBA [email protected] 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!

Wabbash In that case I would suggest to differentiate large databases say more than 1Gb in one plan and small databases on one plan. If not only create a seperate plan for the troubled database (I mean for that database where the plan was not deleting the previous backup files). HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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!!!

True, referring that KBA helps to find out the root cause if not the solution. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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> . 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 MeanOldDBA [email protected] 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)

Interesting quote you have there on your homepage. At least for me as a German. So, you’re located in Barcelona? I would love to go there and have some Gaudi. (Sorry, play on word in German [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] So far we managed only to get to Andalusia, which is quite nice when you leave out Malaga and Marbella. –Frank http://www.insidesql.de</a>

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 …..

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by wabbash 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!! <hr height="1" noshade id="quote"></blockquote id="quote"> We made a roundtrip some years ago. In Malaga we landed and picked up the car. We didn’t stay there. After seeing Cordoba, Granada, Sevilla we came from Ronda to Marbella and had booked a hotel 10 km outside of Marbella. That is definitely our kind of flair there in Marbella. After arriving there we decided to leave ASAP and spent our time better somewhere else, that is here<a target="_blank" href=http://www.allspainaccommodation.com/andalucia/laposadadeltorcal/>http://www.allspainaccommodation.com/andalucia/laposadadeltorcal/</a> This is one of the loveliest hotel I’ve ever seen so far. The right place to relax and enjoy life. Sigh! <blockquote id="quote">quote:<hr height="1" noshade id="quote"> 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 ….. <hr height="1" noshade id="quote"></blockquote id="quote"> I’ll take you for granted on this! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] –Frank http://www.insidesql.de</a>

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. 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. 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. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

You did start with 5 weeks holidays??? One might consider disliking you for saying that without being ashamed. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Last time I had more than 3 weeks was after finishing school. That is some years now. –Frank http://www.insidesql.de</a>

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)

Well, it’s okay for me. A little off-topic talk is always welcome. Do we have a forum for this already. [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Nothing like the dreaded Yak corral on SQL Team, but something in that direction. –Frank http://www.insidesql.de</a>

Come on Frankkkkkkkkk I have seeen the Hotel you were in ANDALUSIA and that has nothing to do with a backpackers standard dormitory!!! It has a grass ground for playing tennis, in the driest place of Europe!! 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 …….

Answering my own question:
Yes, we have!
–Frank
http://www.insidesql.de

Well, they have superb wine, very good cooking and anything else you need to feel somehow decadent [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Btw, wrap your smilies in [ ] brackets. Snitz will parse that and replace with the ones who see when you don’t use the quick reply feature. –Frank http://www.insidesql.de</a>

Sorry to spoil your travelling talks guys… sounds very interesting. <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> I was just planning to post this entry, when I came accross your active one: " 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". 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? " 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
[email protected] 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

So far so good at my end and never had such issues, only the difference is I handle the huge databases (>2GB) on an individual plan and small ones on one plan. This gives more chance to dig out any root cause for the issues. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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.

Why not create a script on your own to delete those old backups from the directory. In SQL 2005 try using Maintenance plan designer to modify the plan & cleanup task. In sQL 2000 you need to edit the MP. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>