November 5, 2007 at 5:26 am
I have a monthly maintenance plan that performs several steps on my user databases only.
Should I also have a maintenance plan for the system databases? If so, which steps should I execute?
Using SQL Server Standard Edition.
Thanks.
November 5, 2007 at 5:48 am
Patrick Womack (11/5/2007)
I have a monthly maintenance plan that performs several steps on my user databases only.Should I also have a maintenance plan for the system databases? If so, which steps should I execute?
Using SQL Server Standard Edition.
Thanks.
You should, just create a maintenance plan for your system databases the same way you've done it on your user DBs.
November 5, 2007 at 6:09 am
Thanks, Leo.
One more question.
I have these steps for my user database maintenance plan:
1. Check Database Integrity
2. Reorganize Index
3. Rebuild Index
4. Update Statistics
5. Clean Up History
6. Back Up Database (Full)
Should I have all of these steps for the system database maintenance plan?
November 5, 2007 at 11:45 am
Will anyone answer this?
November 5, 2007 at 1:33 pm
Patrick,
Keep in mind that most (if not all) of the posters here have full time jobs outside of these forums. Heck, even Steve has other responsibilities. A little patience will get you far.
Regarding your maintenance plan, I have a few questions:
What is/are the recovery model(s) of your databases?
Are you doing transaction log backups?
How often do you modify the DDL?
What is your criteria for the index rebuild step?
Some general comments:
MODEL generally only needs to be backed up / maintained when you make changes to it.
MASTER and MSDB should be backed up / maintained more frequently, but generally not as much / as often as your user databases.
What is your SLA? How busy are your DB's?
While there are general guidelines for maintenance, the actual implementation is very site-specific.
I strongly suggest you go out and buy a good book or two on administering SQL Server and doing some research on your own. I'm a huge fan of Professional SQL Server Administration (by Brian Knight and many others) but that is a more advanced book. The Microsoft Press books on SQL Server administration are pretty good, too.
Online, read through this site, the msdn forums, and the various blogs that are out there.
November 5, 2007 at 2:38 pm
Thanks, Pam.
I hope that I didn't come across as being impatient. I really wasn't, and I appreciate the help I receive here.
To answer your questions.
What is/are the recovery model(s) of your databases?
All simple recovery.
Are you doing transaction log backups?
No. Our databases are for reporting purposes and get refreshed (truncated and re-populated) daily and weekly.
How often do you modify the DDL?
Very rarely.
What is your criteria for the index rebuild step?
Not sure. Kept the default values when I setup the plan. The radio button for "Reorganize pages with the default amount of free space" is selected.
What is your SLA? How busy are your DB's?
SLA? Service Level Agreement? None.
DB's are not very busy. Usually a stored proc is executed in the morning to truncate and populate the tables.
Several smaller databases may get several dozen calls a day.
Thanks again for your time and help.
November 5, 2007 at 10:24 pm
Yes, SLA is Service Level Agreement. A backup once a month seems awfully thin even for a reporting DB. Even if you don't have a formal SLA I am sure your users have certain expectations regarding availability.
If you're going to rebuild an index, there's really no sense in reorganizing it. The one pretty much cancels out the other. Most index maintenance plans will do a reog and then a rebuild of those that are still fragmented after the reorg. Here's something else to keep in mind:
An index rebuild will do the equivalent of an update stats with a full scan. A manual update stats will use whichever sampling rate was set for that particular set of statistics. So - not only does doing an update stats after an index rebuild waste resources, you may actually end of with a worse set of stats if the manually update stats only does a sampled scan.
To answer your original question, I'd go with a single backup of MODEL with a standard maintenance package of msdb and master roughly monthly.
November 6, 2007 at 5:00 am
Thanks for the information, Pam. I will take your advice and purchase a book on administration.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply