| Author |
Topic |
|
qsac
Starting Member
2 Posts |
 Posted - 2005-11-18 : 15:05:44
|
| Has anyone found a way to delete old backups in the maintenance plans?I figured out how to do this, but it does not go into individual folders if each database is backed up in individual folders.Any figure this one out yet? |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-11-18 : 16:56:06
|
| it does go into individual folders.I recommend against using the maintenance plans though, as they are not that robust and seem to fail for no apparent reason.Tara has some scripts that you can use in her blog that handle most of the functionality of a the maintenance plans. Here is the link http://weblogs.sqlteam.com/tarad/category/95.aspx-ec |
 |
|
|
qsac
Starting Member
2 Posts |
Posted - 2005-11-18 : 17:23:23
|
| Can you tell me how it goes into each sub folder. When i create the task, it only allows me to select a direct folder.The maint plan is much different than 2000I do use script more often than not, i just wanted to know how the new maint plans workthanks,Q |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-18 : 20:20:56
|
| "I recommend against using the maintenance plans though, as they are not that robust and seem to fail for no apparent reason."They STILL don't work well in SQL2005?That's sad to hear ...Kristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-11-18 : 20:23:16
|
quote:
Originally posted by Kristen
"I recommend against using the maintenance plans though, as they are not that robust and seem to fail for no apparent reason."They STILL don't work well in SQL2005?That's sad to hear ...Kristen
maybe I'm just biased, but I still don't trust them. Microsoft did completely re-work the interface though. -ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-18 : 20:30:16
|
| "Microsoft did completely re-work the interface though"Hmmm ... it wasn't the interface I had a problem with! |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-18 : 22:36:48
|
| I think it's a little bit crazy to use the Maintenance Plans in 2005 personally. You don't seem to be able to export them (I would love for someone at MS to show me I'm wrong); and you can do the exact same thing with SSIS and just schedule an execution of the package. I DO like SSIS a lot. Another option is to continue using scheduled scripts, especially if you can just grab something like Tara has on her weblog and schedule it.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2005-11-21 : 12:54:59
|
| For someone who has been administering for no more than a year, what is it about Maintenance Plans, one should avoid/mistrust? I've never had a problem with them, and find them convenient? Is this a junior attitude towards them? Is there something I should know?Drew |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-21 : 13:55:50
|
| "Is this a junior attitude towards them"No, I wouldn't put it like that. However, it is a little "out of sight out of mind"A peeve of mine is that they can't be set up under MSDE (the "delete after ... days" picklist is empty) - maybe that's deliberate? But that apart:I think they are very poorly written, and without any good reason that I can think of.What they "promise" is, I grant you, very good indeed.However:Set up a plan for "All databases". Have some databases set to SIMPLE and others to FULL - so the "Full" ones need Tlog backups, the Simple ones will not allow Tlog backups. In this scenario Maint Plan will fail at the end of the Tlog backups and will not do the bit about deleting old TLog files - so one day your disk will be full.Put some of your databases "offline" or "readonly" and the stuff about "Fix minor errors in the database" will fail because it cannot write to the database - it clearly does not check that the database might be read only / not available, which I would have thought is obvious.Select the "cleanup my indexes for me" and the default will change your "100% fill factor" to "90% fill" - which is just plain daft as most indexes will be IDENTITY or something where 100% fill is the better choice. The other radio button does the right thing, but it is NOT the default.There is nothing to delete the entries from the MSDB database of Backup History. And when you find that that database is massive the provided Stored Procedure to "purge" the entries will take WEEKS (I kid you not!) to delete the entries - it is appallingly badly written.Now, none of these are rocket science. Someone who has hammered SQL Server reasonably would uncover these issues. So why not the people responsible for the Maintenance Plans bit of SQL Server??And given these then goodness knows what other issues there are which I didn't find before I was driven to write my own processes to replace the Maint Plans.Kristen |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-21 : 14:38:00
|
| ...or create a maintenance plan, and then open up the job it generates and edit it in some way. Then open up the maintenance plan and you will see that it is completely unaware of the change made to the job. Then edit and regenerte the maintenance plan and you will find the changes you made to your job overwritten.The maintenance plan is basically a wizard for createing jobs. You should create the jobs yourself.The one thing I do find handy about maintenance plans is their ability to group together databases for adminstrative purposes. For instance, I create two maintenance plans on my server called AllDatabases and UserDatabases. The plans do absolutely nothing, but I can refer to the plans by name in my xp_sqlmaint calls and apply the process to all the databases they include. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-22 : 02:03:50
|
| "AllDatabases and UserDatabases"I find it a bit of a blunt weapon!We have a table of database names, and what actions are to be taken on them. By default a newly added database will automatically be added to the "most aggressive backup strategy" - it can then be moved to a "less aggressive one" - including "no backups" if required.It also does things like "If the database name starts with "RESTORE_" then don't back it up - so when we restore a 5GB database just to have a look at the data we can choose a database name that doesn't cause the backup system to start clogging up the disk!FWIW we also have a naming convention of suffixing things like "_LIVE" or "_TEMP" to database names; those cause an appropriate backup strategy to be applied the first time a new database name is seen.We also have an SProc that makes a backup on a database; it has parameters for Full/Diff/TLog, and things like "Retention Date". So before I do a "risky" maneuver I can pop a quick Diff backup; or I can do a full backup with a longer-than-normal retention period - e.g. maybe a year for a backup of a QA database before installing a major upgrade on it. All backups made in this way get purged by the normal process - so I don't have to worry about disk maintenance etc.Kristen |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-22 : 10:52:43
|
| Of course, you are not limited to "AllDatabases" and "UserDatabases". You can set up custom database lists as well, and still run xp_sqlmaint commands against the entire batch. |
|
|
|
|
Maintenance Plans