| Author |
Topic |
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
 Posted - 2008-02-07 : 19:44:32
|
| Hello everyone!I was wondering if there were any best practices for creating maintenance plans?Im just getting started into the DBA world and have been delegated the task of creating maintenance plans for our 8 SQL servers.Right now, our backup policy is Fulls on Saturday, differentials Monday-Friday.Also, since im new, if you defragment the database and rebuild the index, does that have the possibility of "breaking" anything?Just looking for some good articles, or anything to get me started on best practices.Much appreciated.TCG |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-07 : 20:11:44
|
| Are you using SQL server 2000 or 2005.The best way:1) create 1 maintenance plan only for backups2) create maintenance plan for optimization and integrity.3) create another plan for backup of system databasebut with different schedule and times.Yes in 2000, if you rebuild index,your database will be offline if it is big.but in SQL server 2005 enterprise edition, you can do online indexing and online restore. |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-07 : 20:20:48
|
| You can have one maintenance plan and run each step in different schedule. By the way, online restore is only for piecemeal restore. |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2008-02-07 : 20:31:17
|
| Sorry. Meant to say this will be mosty 2005.I did see that you can string together a few items in one maintenance plan (in designer). One finishes, then the next one fires off. Was not sure if that was a good way to do it or not.Appreciate it. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-07 : 20:59:39
|
| What you said is running them in single schedule. |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2008-02-11 : 15:21:33
|
| Ok. Appreciate the help.Best practices to have seperate maintenance plans for backing up user databases and system databases?I just finished creating a maintenance plan for our user databases that include:-DB integrity check-update statistics-reorganize index-rebuild index-BackupFires off nightly.So would it be better to create a new backup maintenance plan, just for the system databases?Appreciate it.TCG |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-11 : 22:42:58
|
| You can do that, but not necessary. |
|
|
|
bozopriester
Starting Member
1 Post |
Posted - 2009-01-22 : 16:11:40
|
| Both rebuild AND reorganize in the same maintenance plan? Doesn't REBUILD blow away the index completely and recreate it, making a prior REORGANIZE pointless?...or am I missing something? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2009-01-22 : 18:56:05
|
| Also I noticed in your plan you are updating the stats. I don't think this is necessary but perhaps others can comment.An index rebuild will do the equivalent of an update stats with a full scanAccording to this blog..[url]http://sqlug.be/blogs/wesleyb/archive/2007/11/13/update-statistics-before-or-after-my-index-rebuild.aspx,/url]r&r |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Maintenance plan help