"One or more SQL Database consistency checks have failed" is displayed in the job log while performing the backup or restore of a SQL Database with 'database consistency check' enabled.

Article:TECH43449  |  Created: 2005-01-01  |  Updated: 2014-03-10  |  Article URL http://www.symantec.com/docs/TECH43449
Article Type
Technical Solution


Subject

Issue



"One or more SQL Database consistency checks have failed" is displayed in the job log while performing the backup or restore of a SQL Database with 'database consistency check' enabled.


Error



V-79-57344-65085 - There was a problem running the DBCC. DBCC failed because the following SET options have incorrect settings: 'ARITHABORT'

V-79-57344-65085 - There was a problem running the DBCC.


Solution



Backup Exec performs following checks on the SQL database, when running Database Consistency Check on it:

 

  • CHECKDB
  • CHECKCATALOG
  • CHECKFILEGROUP
  • PHYSICAL_ONLY

          
    This check is run by passing the control to SQL and Backup Exec uses SQL as an interface to perform this check.


    DBCC CHECKDB requires the following SET options if an index on a computed column exists in the database or table:


    ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON.      

    NUMERIC_ROUNDABORT must be set to OFF.      


    The error message is more likely to occur if the CHECKDB or CHECKTABLE is being scheduled from a SQL Server Agent job or from an Integrity Check in a Database Maintenance Plan. This is because by default the SQL Server Agent does not set QUOTED_IDENTIFIER or ARITHABORT. To schedule a DBCC CHECKTABLE or CHECKDB integrity check on the database, a SQL Server Agent Job must be created and in the Transact-SQL command, the needed SET OPTIONS must be added, as in the following example:

    SET ARITHABORT ON
    SET QUOTED_IDENTIFIER ON
    DBCC CHECKTABLE (mytable)
    go

    Alternatively, this error message can be seen by running following query via Management studio:
     
  • DBCC CHECKDB([DataBasename]) WITH PHYSICAL_ONLY

    or configuring a maintenance job on the SQL Server using Enterprise Manager Management Studio as explained below:
    In order to create a maintenance job, SQL Enterprise Manager is to be opened and a job can be started by going to management as shown in the following (Figure 1)

    Figure 1



    Right Click on Database Maintenance Plans and click on "New Maintenance Plan" as shown below (Figure 2):

    Figure 2



    This opens Database Maintenance Plan wizard, which creates a database maintenance plan job as explained in following slides (Figures 3 thru 11):

    Figure 3



    Figure 4



    Figure 5



    Figure 6





    Figure 7





    Figure 8





    Figure 9





    Figure 10





    Figure 11





    This will create a maintenance job which can be viewed by going to jobs under SQL Server agent as shown below (Figure 12):



    Figure 12





    In order to start the job right click on the job and click on start job as shown below (Figure 13):



    Figure 13






    This will run a job using the SQL Server Agent which is a similar interface used by Backup Exec and will also generate the same error "DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT' which can be resolved by turning ON ARITHABORT or some other computing functions.

    For further details on this, review the following Microsoft Article:


    SET OPTION considerations when running DBCC with indexes on computed columns
    http://support.microsoft.com/kb/301292 


    If above information does not resolve the issue, a support case should be opened with Microsoft.

Supplemental Materials

SourceUMI
ValueV-79-57344-65085
Description

I . "One or more SQL Database consistency checks have failed" is displayed in the job log while performing the backup or restore of a SQL database with 'database consistency check' enabled.

II. There was a problem running the DBCC.

 


SourceError Code
Value0xe0008443
Description

One or more SQL Database consistency checks have failed.
 



Legacy ID



278799


Article URL http://www.symantec.com/docs/TECH43449


Terms of use for this information are found in Legal Notices