December 18, 2015 at 6:01 am
Dear all,
I set up the following script to run on sql agent job to run daily but I get an error message without any obvious indication of where the job failed
EXEC sp_MSforeachDB 'DBCC CHECKDB (?) WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY':
Message
Executed as user: NT AUTHORITY\SYSTEM. ... Service Broker Msg 9675, State 1: Message Types analyzed: 14. [SQLSTATE 01000] (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 822 rows in 11 pages for object "sys.sysrscols". [SQLSTATE 01000] (Message 2593) DBCC results for
'sys.sysrowsets'. [SQLSTATE 01000] (Message 2536) There are 116 rows in 1 pages for object "sys.sysrowsets". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysallocunits'. [SQLSTATE 01000]
(Message 2536) There are 128 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.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 41 rows in 1 pages for object "sys.sysdbfrag". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysfgfrag'. [SQLSTATE 01000] (Message 2536) There are 2 rows in 1 pages for object "sys.sysfgfrag". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.syspru'. [SQLSTATE 01000] (Message 2536) There are 41 rows in 1 pages for object "sys.syspru". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysbrickfiles'. [SQLSTATE 01000] (Message 2536) There are 87 rows in 9 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 17 rows in 1 pages for
object "sys.sysowners". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysdbreg'. [SQLSTATE 01000] (Message 2536) There are 41 rows in 1 pages for object "sys.sysdbreg". [SQLSTATE 01000] (Message
2593) DBCC results for 'sys.sysprivs'. [SQLSTATE 01000] (Message 2536) There are 2012 rows in 15 pages for object "sys.sysprivs". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysschobjs'.
[SQLSTATE 01000] (Message 2536) There are 74 rows in 3 pages for object "sys.sysschobjs". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.syslogshippers'. [SQLSTATE 01000] (Message 2536)
There are 0 rows in 0 pages for object "sys.syslogshippers". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.syscolpars'. [SQLSTATE 01000] (Message 2536) There are 659 rows in 14 pages for object
"sys.syscolpars". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysxlgns'. [SQLSTATE 01000] (Message 2536) There are 89 rows in 2 pages for object "sys.sysxlgns". [SQLSTATE 01000] (Message 2593)
DBCC results for 'sys.sysxsrvs'. [SQLSTATE 01000] (Message 2536) There are 2 rows in 1 pages for object "sys.sysxsrvs". [SQLSTATE 01... The step failed.
Would someone let me know how I would be able to find out what the exact error is please as I cannot see any obvious error from the above message?
Thank you in advance!
December 18, 2015 at 6:15 am
Run the following for each DB, post the full and complete results.
DBCC CheckDB(<database name>) WITH NO_INFOMSGS, ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY
And maybe change your job to include the NO_INFOMGSG option. The number of rows and pages of each table is of no interest.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2015 at 9:11 am
Thank you for your reply!
I created a log file so I can have further information regarding the error messages and I found the following error when running the following data integrity script:
EXEC sp_MSforeachDB 'DBCC CHECKDB (?) WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY'
Msg 319, Sev 15, State 1, Line 1 : Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. [SQLSTATE 42000]
December 29, 2015 at 9:58 am
Perhaps you have a database name (the "?") that cannot be parsed.
Try
EXEC sp_MSforeachDB 'USE [?]; DBCC CHECKDB (0)
WITH NO_INFOMSGS, ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY'
However, it appears at least one version of the undocumented sp_MSforeachDB fails, after handling CHECKDB errors (I have Paul Randal's corrupt databases attached to my system). That means you will only be able to see the tip of the iceberg. And that means you may wish to consider a supportable alternative to sp_msforeachdb (there are one or more articles about alternatives to sp_msforeachdb on this site).
If you are going to use a job, use a CmdExec job step (not a Transact-SQL job step) that calls sqlcmd - similar to what Ola Hallengren's database maintenance jobs do. If each database is checked in a different step (via sqlcmd), the CHECKDBs should be able to carry on, regardless of whether one CHECKDB step raises errors, or not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply