I have a Maintenance Plan set to run every night that Optimizes the databases, runs an Integrity check and then backs them up. Each job is staggered to run 1 hour apart. It has been running fine for months and now I get the following intermittent error during the "Reorganize data and index pages" part:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
I know what database it is failing on but I run dbcc checkdb and find no errors. From what I have found on the web this error pertains to an index problem. The only indices on this database reside in the system tables. I have dropped the database and restored it but no help there. Any ideas?
Do you have indexes on views or computed columns ? If so,
you will need to run dbcc dbreindex or indexdefrag. This occurs because sql server agent does not (by default) set arithabort and quoted_identifier on. So, you will need to create a job that sets these:
set arithabort on
set quoted_identifier on