Sp_forEachDB ' DBCC Check DB' - why does it only check the first DB?
Question: What's the right way to do sp_foreachdb 'dbcc checkdb'?


Don't use sp_msforeachdb. If you're dead set on using it, though (given the many known drawbacks and issues) then you'll need to set the database context before attempting to run checkdb without a database name, or use the database name parameter.

Assume: exec sp_MSforeachdb 'SELECT DB_NAME()'

The output of this will be 'master' however many times as the number of databases that reside on the instance. Simple put, the stored procedure gives you the database name and will run it (hopefully for each database, though known to skip) but not set the context.

Output: master, master, master, master,...

Assume: exec sp_MSforeachdb ' use [?]; SELECT DB_NAME()'

The output of this will be the names of the actual databases (again, hopefully it didn't skip any) on the instance.

If you'd like to run CheckDB this way, you could use:

exec sp_MSforeachdb ' use [?]; DBCC CHECKDB()'
OR
exec sp_MSforeachdb 'DBCC CHECKDB([?])'


  Answer: From SpBlitzErik - use Ola hallengren's scripts, you dummy. 


Also valid and a much better solution.


  1.How often do I need to run it? It needs to run at the same interval that I keep my backups, right? If I keep the backups for 1 day, I need to run it every day? 


IMHO totally depends on the environment and the performance hit. Regardless I'd say once a week (for a total check, not necessarily all at once) is the minimum. Once a day is ideal but may be overkill, especially on large databases where a good portion of the data is read only/archive.


  2.Can I restore the databases to my test environment and checkDB there?


Yes, it'll test your backups and the database but won't test the primary server. This means you might not have any issues on the test server but the production server may have intermittent IO issues - this won't show that. Still better than nothing and you're testing backups :)


  3.If I've got lots of sql servers, but they all use the same SAN, am I going to want to stagger my CheckDB's?


Depends on how much you like/dislike/abhor your SAN admins. If you want to piss them off, fire them all off at the same time and listen to them complain about their enterprise class SAN not being able to handle an enterprise class workload. Otherwise, yes, stagger them.