Which system databases should I checkdb and defrag indices?

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

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.

StackExchange Database Administrators Q#39844, answer score: 5