Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
thanks, Aaron! great answer. –  SFun28 Apr 12 '13 at 17:12
    
Also since you are on SQL Server 2005 I might recommend these indexes from Geoff Hiten. (Microsoft wisely added them in more modern versions.) –  Aaron Bertrand Apr 12 '13 at 17:23
    
Aaron - much appreciated! This is super useful! –  SFun28 Apr 12 '13 at 17:29
add comment

All system and user databases should be check using CHECKDB. The only exception will be tempdb as you cannot generate a snapshot of tempdb and checkdb behind the hood generates a DB Snapshot.

The tricky part is for tempdb (When Tempdb is corrupted) as we ALL know that it gets recreated when a SQL Server restart is done, but Tempdb files are not deleted when you restart SQL Server and are also not 0 (zero) initialized so the corruption can persist.

Tempdb corruption (which is rare .. and most likely will be due to a bug in SQL Server) is described here.

share|improve this answer
    
much appreciated! –  SFun28 Apr 12 '13 at 17:19
add comment

Defragging using native windows tools could cause more problems than good. Stick with checkdb on master, msdb, and model. Tempdb gets recreated on startup. The master db is critical and if you get model corrupted it migt be hard to make new DBs but its easily recreatable unless you highly customized it and didn't back it up. If you never change model just make sure you have a good backup.

share|improve this answer
    
Ali - thanks so much. I defrag using SQL commands (updated post to reflect that) –  SFun28 Apr 12 '13 at 16:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.