This article was previously published under Q301292
DBCC CHECKTABLE, DBCC DBREINDEX, and DBCC CHECKDB may fail with the following error message if the database contains a table that has an index on a computed column:
DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
This behavior is by design.
DBCC CHECKDB, DBREINDEX, and CHECKTABLE require 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, you must create a SQL Server Agent Job and in the Transact-SQL command you must add the needed SET OPTIONS as in the following example.
SET ARITHABORT ONSET QUOTED_IDENTIFIER ONDBCC CHECKTABLE(mytable)go
Here is a sample script describing how to run DBCC DBREINDEX on the entire database.
SET ARITHABORT ON SET QUOTED_IDENTIFIER ON use MyDatabase -- CHANGE THE DATABASE NAMEgo declare @tabname sysname declare @dbstring varchar(300) declare @exec_string varchar(300) declare tabDBCC cursor for select table_name from information_schema.tables where table_type = 'base table' open tabDBCC fetch next from tabDBCC into @tabname select @dbstring = DB_NAME() print 'Starting DBCC DBREINDEX for database ' + upper(@dbstring) while (@@fetch_status = 0) begin print 'Reindexing table ' + upper(@tabname) select @exec_string = 'dbcc dbreindex ([' + @tabname + '])' exec(@exec_string) fetch next from tabDBCC into @tabname end close tabDBCC deallocate tabDBCC print 'Finished DBCC DBREINDEX for database ' + upper(@dbstring) go
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
902388 Event ID: 208 may be logged, and a "DBCC failed" error message is logged when you try to use the Database Maintenance Plan Wizard to create a maintenance plan in SQL Server 2000