"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.
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.
Backup Exec performs following checks on the SQL database, when running Database Consistency Check on it:
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)
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)
Right Click on Database Maintenance Plans and click on "New Maintenance Plan" as shown below (Figure 2):
This opens Database Maintenance Plan wizard, which creates a database maintenance plan job as explained in following slides (Figures 3 thru 11):
This will create a maintenance job which can be viewed by going to jobs under SQL Server agent as shown below (Figure 12):
In order to start the job right click on the job and click on start job as shown below (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
If above information does not resolve the issue, a support case should be opened with Microsoft.