March 26, 2010 at 11:39 am
my database integrity job has failed,stating the below iam using the script for this i found in viewhistory as
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) DBCC results for 'sys.sysrowsetcolumns'. [SQLSTATE 01000] (Message 2536) There are 714 rows in 7 pages for object "sys.sysrowsetcolumns". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysrowsets'. [SQLSTATE 01000] (Message 2536) There are 104 rows in 1 pages for ... The step failed.
and on the output to a textfile i found nothing except statements of these type
There are 1 rows in 1 pages for object "sys.sysrts". [SQLSTATE 01000]
DBCC results for 'sys.sysconvgroup'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object "sys.sysconvgroup". [SQLSTATE 01000]
DBCC results for 'sys.sysdesend'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object "sys.sysdesend". [SQLSTATE 01000]
DBCC results for 'sys.sysdercv'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object "sys.sysdercv". [SQLSTATE 01000]
DBCC results for 'sys.syssingleobjrefs'. [SQLSTATE 01000]
There are 1477 rows in 11 pages for object "sys.syssingleobjrefs". [SQLSTATE 01000]
DBCC results for 'sys.sysmultiobjrefs'. [SQLSTATE 01000]
There are 102 rows in 1 pages for object "sys.sysmultiobjrefs". [SQLSTATE 01000]
DBCC results for 'sys.sysdbfiles'. [SQLSTATE 01000]
March 26, 2010 at 1:02 pm
Please run the following on all of the database that the integrity task runs against and post any results.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
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
March 26, 2010 at 4:43 pm
this is a prod server and dbcc checkdb dosent gave any errors all dbs have executed with out errors and it is failing once in two or three days in a week
March 27, 2010 at 2:36 am
Then there's something else causing the job to fail. Set the job to write output to a file and check that for the complete output.
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
March 27, 2010 at 11:26 am
The involvement of ServiceBroker seems particularly odd to me. Are you perhaps using Database Mirroring?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2010 at 1:23 pm
what is job doing can you provide job details?
March 27, 2010 at 1:55 pm
RBarryYoung (3/27/2010)
The involvement of ServiceBroker seems particularly odd to me. Are you perhaps using Database Mirroring?
That's normal. On 2008 (and 2005 iirc), when CheckDB is run and the NO_INFOMSG option is not specified, there are a lot of informational messages and the first few refer to Service Broker, even if the broker's not enabled in that database. It's part of the check of the DB catalog.
This is the first few rows from the checkdb output of AdventureWorks in my desktop
DBCC results for 'Adventureworks'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC results for 'sys.sysrscols'.
There are 1556 rows in 19 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 301 rows in 3 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysallocunits'.
There are 345 rows in 5 pages for object "sys.sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
The 14 message types and 6 contracts are system ones.
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
March 27, 2010 at 2:25 pm
Ah. OK, thanks then Gail. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2010 at 3:52 pm
His name is gila and gail <eom>
March 27, 2010 at 5:08 pm
vidya_pande (3/27/2010)
His name is gila and gail <eom>
Not sure what you're trying to say her, but let me clarify: Her name is Gail Shaw, and she has the nickname GilaMonster on these forums.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 29, 2010 at 8:49 am
gail,i have attached the output to a text file but i dont see any error messages on the outputfile,i have attached a copy of it hear can you check it.
March 29, 2010 at 9:01 am
dba_neo (3/29/2010)
gail,i have attached the output to a text file but i dont see any error messages on the outputfile,i have attached a copy of it hear can you check it.
Does this come from a job execution that failed?
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
March 29, 2010 at 9:20 am
yes........
March 29, 2010 at 9:36 am
this was output of the file for the job that has executed successfully on saturday&sunday and failed today.
April 7, 2010 at 7:14 am
any update...
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply