Sql-server – Can sp_MSforeachDB be used for DBCC CheckDB without errors being masked

dbcc-checkdbsql server

I have a test database with an index I intentionally corrupted to test DBCC CheckDB.

When I run: DBCC CHECKDB (Test2) WITH ALL_ERRORMSGS, NO_INFOMSGS, DATA_PURITY

I get, as expected:

Msg 8928, Level 16, State 1, Line 1
Object ID 2105058535, index ID 2, partition ID 72057594038845440, alloc unit ID 72057594039762944 (type In-row data): Page (1:156) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2105058535, index ID 2, partition ID 72057594038845440, alloc unit ID 72057594039762944 (type In-row data), page (1:156). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 2, partition ID 72057594038845440, alloc unit ID 72057594039762944 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:156) and previous child (0:0), but they were not encountered.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'Names' (object ID 2105058535).
CHECKDB found 0 allocation errors and 3 consistency errors in database 'Test2'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Test2).

However, when I run: exec sp_MSforeachDB 'DBCC CHECKDB (?) WITH ALL_ERRORMSGS, NO_INFOMSGS, DATA_PURITY'

I get no errors.

I understand that sp_MSforeachDB is not supported. However, several blogs suggested using sp_MSforeachDB to check all the DB's on a SQL Server Instance.

Question: Am I doing something wrong, and if so, what is the fix, or is this some idiosyncrasy with sp_MSforeachDB?

Best Answer

It sounds like sp_MSforeachDB is skipping your Test2 database. This is one of many known issues with sp_MSforeachDB as noted by Aaron Bertrand:

You can confirm by removing the NO_INFOMSGS parameter: 

exec sp_MSforeachDB 'DBCC CHECKDB (?) WITH DATA_PURITY'

This should cause messages to be returned for every database. Search the results for your Test2 database. I bet it isn't there.

With DBCC CHECKDB all error messages are displayed by default. Specifying or omitting the WITH ALL_ERRORMSGS parameter has no effect.

You may like to look at Ola Hallengren's solutions for SQL Server Backup, Integrity Check, and Index and Statistics Maintenance.

Related Question