SET OPTION considerations when running DBCC with indexes on computed columns
This article was previously published under Q301292
This article has been archived. It is offered "as is" and will no longer be updated.
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.
Here is a sample script describing how to run DBCC DBREINDEX on the entire database.
SET ARITHABORT ONSET QUOTED_IDENTIFIER ONDBCC CHECKTABLE(mytable)go
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
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
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
Article ID: 301292 - Last Review: 12/06/2015 03:17:58 - Revision: 5.3
Microsoft SQL Server 2000 Standard Edition
- kbnosurvey kbarchive kbprb KB301292