1

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 DB's arent 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?

2

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.

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.

  • cool. it seems to be failing when there are no errors, too - or does that mean i'm wrong and there is corruption that i missed? – James May 16 '18 at 17:24
  • 1
    @James the job can fail for reasons other than corruption. – Erik Darling May 16 '18 at 17:46
  • @James how do you know there are no errors? Have you reviewed all the output of the job? – Shawn Melton May 17 '18 at 12:18
  • @ShawnMelton Melton not in detail every day, but Transact-SQL DBCC DBINFO('MyDatabase') WITH TABLERESULTS is giving me today's results – James May 17 '18 at 12:43

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.