Sql-server – DBCC CHECKDB fails and quits job, ambiguous error message

dbccsql serversql-server-2008

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.

Best Answer

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

Related Topic