Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bryan42
Starting Member
28 Posts |
 Posted - 2011-09-09 : 09:18:18
|
| How does one manage the maintenance plans on several hundred SQL Server systems? Are there any commercial or free products for doing this?I'm adding and standardizing maintenance plans. Most use standard maintenance plans configured with the gui, though I'm using a custom script to handle index reorg/rebuild work. Doing this manually on many, many servers gets tedious. Changing all of them in even a minor way is more tedious. How do you do it? |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2011-09-09 : 14:10:37
|
| I once did a master script to create those maintenance plan jobs on the fly for each new instance installed and run it when new database(s) are createdit's a good investment on your part which only costs you time and will benefit you with lots of coffee breaks while the system handles itself :)--------------------keeping it simple... |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-09 : 15:23:31
|
| I have a home-rolled Stored Procedure that does backups "how we like them".It uses an ADMIN database which has a table of databases and the "style" of backup that is required for each. "Styles" cover whether databases need to be backed up every 15 minutes, or less often.If I create a new database it will be added to the ADMIN database at the highest backup frequency - so, at worse, we back-up a new database more frequently than necessary. There is no need to make a new maintenance plan, or "do anything", when a new database is created. But you can change the "style" of backup if the initial default is excessive.Any database called "RESTORE_xxx" is not backed up - so we can restore a database to a temporary name without immediately causing a cascade of backups and disk-space issues!The "style" of maintenance also includes how frequently Index Rebuilds etc are done. Our default is to rebuild any indexes that are small each night, but only if they are fragmented, and to reorganise larger indexes but, again, only if they are fragmented. There are similar housekeeping routines for other "chores".It still amazes me that, after all these years and versions, the Microsoft "wizard" just takes a blunderbuss to database maintenance, e.g. rebuilding all indexes regardless of whether there is any fragmentation, or not, rather than offering a "you create the database, we'll back it up for you regardless" type approach ... |
|
|
|
|
|
|
|
|
Maintenance plan deployment options?