Sp_forEachDB ' DBCC Check DB' - why does it only check the first DB? I set up a checkDB job, and I used sp_foreachDB, thinking I was really clever. Looking at the job in detail, however, it only seems to do the Master Database then stops, reporting success.

I can grow my own like this:

Create procedure JM_CheckDB

as  

declare @DB_Name varchar(255),
@sql nvarchar(max)

declare c cursor local for

SELECT name FROM master.dbo.sysdatabases

open c
fetch from c into @DB_Name

While @@FETCH_STATUS = 0 
begin

set @sql = 'Use ' + @DB_Name + ' DBCC CheckDB'

print @sql
exec sp_executesql @sql

fetch next from c into @DB_Name

end

close c
deallocate c


But I'd rather use the same thing everyone else uses. 

Question: What's the right way to do sp_foreachdb 'dbcc checkdb'?

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

Followup Questions:


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?  
Can I restore the databases to my test environment and checkDB there?
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?
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.