• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

System DBs Maintenance Plan

Is there a best practice approach to this?
I mean what is standard across the option when creating a system db plan.
Obviously back up the DBs but what about the other tabs (SQL 2000). Which are illogical when dealing with system dbs?
0
QPR
Asked:
QPR
  • 3
  • 2
1 Solution
 
tempterCommented:
Hey mate

Check this site out, its a maintenance plan via scripts.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html

I think its great and im going to implement this myself.

GoodLuck
Morci
0
 
QPRAuthor Commented:
thanks but that is for user DBs I'm talking system DBs.
It's also 2005/8
0
 
Scott PletcherSenior DBACommented:
You should definitely back up master and msdb, at least a few times per day.  You can use a standard maintenance job for that; create a separate plan that just does the system dbs and does not include t-log backups, only full db backups.

Also, do a test restore of your system, starting with master (on a test server, of course :-) ).  Restoring master is different than other dbs, and it is best to have practice with it and know how to do it before a real emergency occurs.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
QPRAuthor Commented:
Thanks Scott. For me the backing up of the db and logs are a definate.
I've got many MPs running already but I've usually accepted the defaults and stepped through the wizard.
I just wondered whether the steps were necessary for system DBs.... optimise? free space? integrity? rebuild indexes?

I can make these decisions for user dbs but what is the "norm" when it comes to system DBs?
0
 
QPRAuthor Commented:
Any offers so I can close this Q?
I back up all system DBs nightly. If using a maintenance plan should I include options such as optimise? free space? integrity? rebuild indexes? or are these things irrelevant on system DBs?
0
 
Scott PletcherSenior DBACommented:
Sorry for the delay.

Yes, you can do optimization, integrity checks and rebuild inexes; they are relevant to all dbs.

However, be sure to specify the option that says roughly: "Use original amount of free space" rather than the default "Force/Set free space to 10%".  In fact, the force/set option should NEVER be used, particularly for user dbs, because it could undo tuning done on a table by table basis.  ALWAYS set to use the original amount of fs.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now