Sql-server – sp_MSforeachdb Continue on failure when running DBCC CHECKDB

corruptiondbcc-checkdbmaintenancesql-server-2016

I have the following maintenance script

DECLARE @sql NVARCHAR(30) = 'DBCC CHECKDB(?)'
EXEC sp_MSforeachdb @sql

This checks every database for corruption. I have run this on my test server where I deliberately corrupted a database and as expected, the DBCC CHECKDB errors for that database, however, this then ends the script, so all the other databases that haven't yet been checked go unchecked.

Is there a way I can make the script continue on error so that the remaining databases get checked?

I have also tried Aaron Bertrand's sp_foreachdb and found the behaviour to be the same

1: https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/ as well as Ola.Hallengren's SQL Server Integrity Check using the command DatabaseIntegrityCheck @Databases = 'ALL_DATABASES' which again gives the same behaviour

I have noticed management studio shows "Disconnected" at the bottom also

Best Answer

Since I can't in good conscious provide a solution that is undocumented, unsupported, and likely to skip databases, using my replacement, try adding TRY/CATCH:

EXEC master.dbo.sp_foreachdb 
  @command = N'
    BEGIN TRY
      DBCC CHECKDB(?) WITH NO_INFOMSGS, ALL_ERRORMSGS;
    END TRY
    BEGIN CATCH
      PRINT ''? failed.'';
    END CATCH';

Depending on the severity of the error, though, it's possible that it will sever the connection. The above works if you set two databases offline and then add the parameter @state_desc (required since naturally the procedure skips offline databases):

ALTER DATABASE db1 SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE db2 SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO

EXEC master.dbo.sp_foreachdb 
  @command = N'
    BEGIN TRY
      DBCC CHECKDB(?) WITH NO_INFOMSGS, ALL_ERRORMSGS;
    END TRY
    BEGIN CATCH
      PRINT ''? failed.'';
    END CATCH',
  @state_desc = N'OFFLINE';

Output:

db1 failed.
db2 failed.

If TRY/CATCH still fails in your scenario, you might either want to stop what you're doing and deal with that issue directly before proceeding with "all's ok" checks on the other databases or, at least once you've identified a database with an issue, bypass it altogether using the @exclude_list parameter added in the First Responders Kit:

Related Question