Maintenance Plan - System Databases - More than just backup?

Question

Maintenance Plan - System Databases - More than just backup?

mjcnospam-general

Old Hand

Points: 391
More actions
April 24, 2009 at 8:00 am

#384439

Hi,
Noob question.
Should I go through all the steps of checking, reorganize, rebuild, reindexing, updating stats, etc. for the system databases? Does this make sense? Is it dangerous or slow?
I understand model probably doesn't need this.
Probably tempdb doesn't either.
But master and msdb?
Thanks,
Mike

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I generally include master and msdb in those steps. Definitely don't include tempdb in most/any of them. I don't even back up model unless I've made changes to it, but an integrity check is probably in order (you wouldn't want to create a new database and then find that model had been corrupted some time back).

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

I don't include tempdb in anything.

I do include model. If changes get made there, you want them backed up. However I don't reorganize or reindex system dbs.

I put system dbs in a separate plan from user dbs.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

I put system databases in their own plan - perform integrity checks every night, backup all system databases (this includes model) and remove old backups older than 1 day (we copy our backups to tape every day).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

DKG-967908 SSCrazy Points: 2345 More actions · Answer 4

We used to do intigrity check every day for the system database exludeing model&tempdb and weekly optimization for system database. for user dbs it depends of various things.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Integrity checks of the system databases (master, model, msdb) are highly recommended. Corruption in those can be very hard to fix if you don't have a clean backup, and without regular integrity checks it's pretty easy for corruption to go unnoticed for quite some time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DKG-967908 SSCrazy Points: 2345 More actions · Answer 6

DKG-967908

SSCrazy

Points: 2345

April 25, 2009 at 12:52 pm

#984001

Exactely agree with you Gail.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

DKG (4/24/2009)
We used to do intigrity check every day for the system database exludeing model&tempdb

Why do you exclude model?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DKG-967908 SSCrazy Points: 2345 More actions · Answer 8

Gail, thanks for you concern.

As per my knowledge, The model database is a template, used by Microsoft SQL Server when creating other databases, such as tempdb or user databases. When a new database is created, the entire contents of the model database are copied to the new database. Does it require intigrity check, or just to make sure it is not corrupt - it is required?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

DKG (4/26/2009)
As per my knowledge, The model database is a template, used by Microsoft SQL Server when creating other databases, such as tempdb or user databases. When a new database is created, the entire contents of the model database are copied to the new database.

Yup, that's right

Does it require intigrity check, or just to make sure it is not corrupt - it is required?

Well, what happens if an IO subsystem glitch corrupts model? Furthermore, what happens if, due to high uptime, you only find out some weeks later when the corruption's been backed up several times?

Or what happens if you find that corruption after 2 or 3 databases have been created from the corrupt model and all have the same corruption in them?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DKG-967908 SSCrazy Points: 2345 More actions · Answer 10

Gail, this makes sense to me. Thank you very much for the explanation, much appriciated.

mjcnospam-general Old Hand Points: 391 More actions · Answer 11

Thanks to all who replied. This helped me get on my feet!

Best,

Mike

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 12

mjcnospam-general (4/28/2009)
Thanks to all who replied. This helped me get on my feet!
Best,
Mike

HI Mike,

This is a very good article by Brad M. McGehee

http://www.sqlservercentral.com/blogs/aloha_dba/archive/2009/04/16/the-maintenance-plan-wizard-can-lead-you-astray.aspx

It gives you a brief idea of common mistakes made using Maintenance Plan.

Hope this helps ...

Thanks,

\\K 🙂

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂