0

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

  • 2
    Why not use Ola Hallengren’s scripts instead? Https://ola.hallengren.com – Brent Ozar Nov 12 '18 at 11:47
  • Thanks, I didn't realise that kit had an integrity check. I have tried and see the same issue (see amended question) – SEarle1986 Nov 12 '18 at 12:05
  • Oh wow, sounds like you're hitting corruption so bad that it's severing the connection. In that case, fire up a call to Microsoft support. Sounds like you've already got serious corruption. – Brent Ozar Nov 12 '18 at 12:16
  • Its only a test database I'm working with on a test server which I corrupted using a hex editor. Seems I've done a good job of it! – SEarle1986 Nov 12 '18 at 12:18
0

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.