Why would my checkDB job be reporting failure?

Question

I've got this little script that runs checkdb on every database. When I execute it in SSMS, it works just fine. As a scheduled job, however, it reports this massive, unreadable error:

Message 8997) Service Broker Msg 9676, State 1: Service Contracts analyzed: 6. [SQLSTATE 01000] (Message 8997) Service Broker Msg 9667, State 1: Services analyzed: 3. [SQLSTATE 01000] (Message 8997) Service Broker Msg 9668, State 1: Service Queues analyzed: 3. [SQLSTATE 01000] (Message 8997) Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0. [SQLSTATE 01000] (Message 8997) Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0. [SQLSTATE 01000] (Message 8997) Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0. [SQLSTATE 01000] (Message 8997) Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0. [SQLSTATE 01000] (Message 8997) DBCC results for 'sys.sysrscols'. [SQLSTATE 01000] (Message 2536) There are 1295 rows in 18 pages for object "sys.sysrscols". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysrowsets'. [SQLSTATE 01000] (Message 2536) There are 170 rows in 2 pages for object "sys.sysrowsets". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysclones'. [SQLSTATE 01000] (Message 2536) There are 0 rows in 0 pages for object "sys.sysclones". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysallocunits'. [SQLSTATE 01000] (Message 2536) There are 201 rows in 3 pages for object "sys.sysallocunits". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysfiles1'. [SQLSTATE 01000] (Message 2536) There are 2 rows in 1 pages for object "sys.sysfiles1". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysseobjvalues'. [SQLSTATE 01000] (Message 2536) There are 0 rows in 0 pages for object "sys.sysseobjvalues". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysmatrixages'. [SQLSTATE 01000] (Message 2536) There are 0 rows in 0 pages for object "sys.sysmatrixages". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.syspriorities'. [SQLSTATE 01000] (Message 2536) There are 0 rows in 0 pages for object "sys.syspriorities". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysdbfrag'. [SQLSTATE 01000] (Message 2536) There are 13 rows in 1 pages for object "sys.sysdbfrag". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysfgfrag'. [SQLSTATE 01000] (Message 2536) There are 0 rows in 0 pages for object "sys.sysfgfrag". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysdbfiles'. [SQLSTATE 01000] (Message 2536) There are 2 rows in 1 pages for object "sys.sysdbfiles". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.syspru'. [SQLSTATE 01000] (Message 2536) There are 13 rows in 1 pages for object "sys.syspru". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysbrickfiles'. [SQLSTATE 01000] (Message 2536) There are 31 rows in 4 pages for object "sys.sysbrickfiles". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysphfg'. [SQLSTATE 01000] (Message 2536) There are 1 rows in 1 pages for object "sys.sysphfg". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysprufiles'. [SQLSTATE 01000] (Message 2536) There are 2 rows in 1 pages for object "sys.sysprufiles". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysftinds'. [SQLSTATE 01000] (Message 2536) There are 0 rows in 0 pages for object "sys.sysftinds". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysowners'. [SQLSTATE 01000] (Message 2536) There are 16 rows in 1 pages for object "sys.sysowners". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysdbreg'. [SQLSTATE 01000] (Message 2536) There are 13 rows in 1 pages for object "sys.sysdbreg". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysprivs'. [SQLSTATE 01000] (Message 2536) There are 2363 rows in 17 pages for object "sys.sysprivs". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysschobjs'. [SQLSTATE 01000] (Message 2536) There a... The step failed.

When I query to see the last good CheckDB, it's always today - the DBs aren't actually corrupt.

Here's the script:

    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

Question: Why would this script report failure when it is scheduled as a job?

Answer 1

the output of your DBCC is making the job take it as a failure ([SQLSTATE 01000] (Message 2536) for example).

Solution is to run the dbcc with the NO_INFOMSGS option, as seen below:

dbcc checkdb(yourdatabase) with NO_INFOMSGS

I have several jobs running this way with no issues.

Answer 2

Running DBCC CHECKDB via a SQL Agent job and it will fail when it returns errors in the integrity of the database that was checked. It will do this when you run it via SSMS query as well.

You should set an output file to that job and then you can review the output more easily in Notepad. The job history output will be truncated and is not going to be able to hold all the output that CHECKDB spits out.

You can prevent a lot of that output by adding WITH NO_INFOMSGS to the command, but still a good idea to have the output go into an output file on the job.