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 only takes a minute to sign up.
Sign up to join this community
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?
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.
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.
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.
