Automating DBCC CHECKDB

Question

I am currently trying to script a job on SQL Server 2005 that will automate the DBCC CHECKDB process. Basically, I am using a cursor to run through and run DBCC CHECKDB on every database on an instance. Sometimes it works, running through every database and logging the errors in a table I have designed for that purpose and sometimes it only runs through a few of the databases and stop. Does anyone have any idea what is going on? I have included the code that I use for the cursor.

DECLARE @DbName varchar(100)

DECLARE GetDbName CURSOR LOCAL FORWARD_ONLY OPTIMISTIC FOR SELECT name FROM sys.databases ORDER BY name

OPEN GetDbName FETCH NEXT FROM GetDbName INTO @DbName WHILE (@@fetch_status = 0) BEGIN

    print @DbName

    INSERT INTO
      TempLog
    EXEC('DBCC CHECKDB ('+ @DbName +') WITH NO_INFOMSGS, TABLERESULTS')

    FETCH NEXT FROM GetDbName
    INTO @DbName

  END

CLOSE GetDbName DEALLOCATE GetDbName

Answer 1

Errors of a sufficiently high (20+) severity -- in my experience these are most often corruptions such as invalid text pointers -- will stop whatever SQL job is currently running with extreme prejudice, ignoring try/catch constructs and killing the connection. I would suggest moving this task out to an external process with a new connection for each DBCC CHECKDB command so that it can continue in the face of high-severity errors.

Answer 2

use the undocumented sp_MSforeachdb stored proc

exec sp_msforeachdb 'use ?; dbcc checkdb'