Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I received a notice that one of our servers (SQL Server 2008 Cluster on Win Enterprise 2008 Cluster) DBCC CHECKDB for all databases has been failing the past four times it has been run. We don't have any data prior to that, but it doesn't look like it has been succeeding for awhile. There are no errors in the log file only:

DBCC results for 'sys.sysxmlfacet'. [SQLSTATE 01000]
Msg 0, Sev 0, State 1: Unspecified error occurred on SQL Server. Connection may have been     terminated by the server. [SQLSTATE HY000]
There are 112 rows in 1 pages for object "sys.sysxmlfacet". [SQLSTATE 01000]

I ran a DBCC CHECKDB using sp_MSForEachDB to get more accurate results and had the same error on the same DB but at a separate point:

DBCC results for 'NameValuePair_Greek_CI_AS'. [SQLSTATE 01000]
Msg 0, Sev 0, State 1: Unspecified error occurred on SQL Server. Connection may have     been terminated by the server. [SQLSTATE HY000]
There are 0 rows in 0 pages for object "NameValuePair_Greek_CI_AS". [SQLSTATE 01000]

Also, the error-log states that the DBCC completed without errors for this database.

I can't figure out how to track down this ambiguous issue that only happens on this database out of the dozens on this server. Any help is appreciated!

Edit to answer comment:
It is being run locally via SQL Server Agent. The only thing we thought it might be is since it is on a Win2008 cluster that it might be failing over since the db is too large right now (880GB, but not as large as many other db running same job on same hard/software), but we have nothing to show a failover is occurring which takes out connectivity issues. A failover usually sends off so many flags and warnings they're hard to miss. Breaking up the db might be our next troubleshooting measure, but until then we are just trying to understand why this job is running so oddly.

share|improve this question
    
It's not connection related, is it? Can you run it right on the server? Or via an agent job? –  SqlACID Apr 21 '10 at 11:06

1 Answer 1

up vote 2 down vote accepted

If the error log says that CHECKDB completed, then it did - that's the very last thing that CHECKDB does before exiting.

This looks like a client issue while printing the results. When I was writing DBCC CHECKDB during SQL Server 2005 development, there were funky issues sometimes with various clients depending on the number of rows of output - e.g. SSMS will only print the first 1000 errors from CHECKDB otherwise it runs out of memory

A few things to try:

  1. run using WITH ALL_ERRORMSGS, NO_INFOMSGS (this is the way I always recommend people run it)
  2. run it using sqlcmd instead of SSMS

  3. run it from SQLCMD and pipe the results to a file using the SQLCMD -o switch

Thanks

share|improve this answer
    
Just to clarify; if the log states that DBCC CHECKDB (for all dbs's) completed without errors, all is ok? Even though not all db's that needed to be checked by the job were not checked because it appears to have quit? –  Ddono25 May 12 '10 at 2:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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