SET OPTION considerations when running DBCC with indexes on computed columns

Article translations Article translations
Article ID: 301292 - View products that this article applies to.
This article was previously published under Q301292
Expand all | Collapse all

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

Properties

Article ID: 301292 - Last Review: December 20, 2005 - Revision: 5.3
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbprb KB301292

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com