Article ID: 301292 - Last Review: December 20, 2005 - Revision: 5.3

SET OPTION considerations when running DBCC with indexes on computed columns

View products that this article applies to.

This article was previously published under Q301292

SYMPTOMS

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'.

MORE INFORMATION

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 ON
SET QUOTED_IDENTIFIER ON
DBCC 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 NAME
go 
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 (http://support.microsoft.com/kb/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