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

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.

  • 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

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.

  • much appreciated! – SFun28 Apr 12 '13 at 17:19

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.

  • Ali - thanks so much. I defrag using SQL commands (updated post to reflect that) – SFun28 Apr 12 '13 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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