Document ID: 278799
http://support.veritas.com/docs/278799
E-Mail this document to a colleague
http://support.veritas.com/docs/278799
E-Mail this document to a colleague
"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.
Exact Error Message
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. DBCC failed because the following SET options have incorrect settings: 'ARITHABORT'
Details:
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 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.
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)
go
Alternatively, this error message can be seen by 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 Material:
| System: Ref.# | Description |
| UMI: V-79-57344-65085 | "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. |
Products Applied:
Backup Exec 10.0, 10.0 5484, 10.0 5520, 10d (10.1), 10d (10.1) 5629, 11d (11.0), 11d (11.0) 6235, 11d (11.0) 7170, 12.0, 12.5, 12.5 .3 SP3, 9.1, 9.1 4691
Backup Exec 10.0, 10.0 5484, 10.0 5520, 10d (10.1), 10d (10.1) 5629, 11d (11.0), 11d (11.0) 6235, 11d (11.0) 7170, 12.0, 12.5, 12.5 .3 SP3, 9.1, 9.1 4691
Last Updated: December 08 2009 08:54 PM GMT
Expires on: 365 days from publish date
Subscribe to receive critical updates about this document
Expires on: 365 days from publish date
Subscribe to receive critical updates about this document
Subjects:
Backup Exec
Agent For Sql: Backup-Restore
Backup Exec
Agent For Sql: Backup-Restore
Languages:
English (US)
English (US)
Operating Systems:
Windows 2000
Windows 2000
Advanced Server SP4, Server SP4
Windows Server 2003
Enterprise SP2, Standard Server SP2
Windows Server 2008
DataCenter (x64-64bit), DataCenter (x64-64bit) SP2, DataCenter (x86-32bit), DataCenter (x86-32bit) SP2, Enterprise (x64-64bit), Enterprise (x64-64bit) SP2, Enterprise (x86-32bit), Enterprise (x86-32bit) SP2, Itanium, Itanium SP2, Server Core, Server Core SP2, Standard (x64-64bit), Standard (x64-64bit) SP2, Standard (x86-32bit), Standard (x86-32bit) SP2, Web Server (x64-64bit), Web Server (x64-64bit) SP2, Web Server (x86-32bit), Web Server (x86-32bit) SP2
Symantec World Headquarters:
20330 Stevens Creek Blvd. Cupertino, CA 95014
World Wide Web: http://www.symantec.com,
Tech Support Web: http://entsupport.symantec.com,
E-Mail Support: http://seer.entsupport.symantec.com/email_forms,
FTP: ftp://ftp.entsupport.symantec.com or http://ftp.entsupport.symantec.com
20330 Stevens Creek Blvd. Cupertino, CA 95014
World Wide Web: http://www.symantec.com,
Tech Support Web: http://entsupport.symantec.com,
E-Mail Support: http://seer.entsupport.symantec.com/email_forms,
FTP: ftp://ftp.entsupport.symantec.com or http://ftp.entsupport.symantec.com
THE INFORMATION PROVIDED IN THE SYMANTEC SOFTWARE KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. SYMANTEC SOFTWARE DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL SYMANTEC SOFTWARE OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES,EVEN IF SYMANTEC SOFTWARE OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.