I have SQL Server Agent (2005) jobs that periodically perform CHECKDB and a defrag (ALTER INDEX REORGANIZE/REBUILD) of any index that is highly fragmented. These are typical maintenance best practices. I'm wondering which of the system databases I should apply these jobs to? Right now I run CHECKDB only on master, and I vaguely remember setting it up because I saw that in a book somewhere.
So, for which of the following should I perform CHECKDB and/or index defrag?
- master
- model
- msdb
- tempdb
Best Answer
In my mind general checkdb would really only apply to master and msdb. You shouldn't have volatile data in model or tempdb (and Kin points out a good article on corruption in tempdb - should be much less of an issue if you move to a more modern version of SQL Server). I wouldn't bother doing this routinely against tempdb, but only deal with it if you actually get reports of corruption. IMHO. If you've added user objects to model so that they are created in every new database, that might increase the risk there so you may want to include it. Typically it is quite small so it doesn't really hurt to include it anyway.
For defrag, MSDB is really the only database you should worry about here (especially if your backup history and job history tables grow unchecked). Master would be a candidate if you put user tables there, but I would rather fix that by removing them altogether than to try to optimize them. As above, you shouldn't have frequently changing data in model, and tempdb should not have any permanent objects either, so defragging there is either wasteful or spurious or both.