Day 20: Maintenance job failed when index on computed column

Description:

When you create a maintenance plan on sql server 2000, it may failed if you have index on computed column. It will failed with either of this following error.

[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’.

The Integrity job is failing only on XXX database due to computed column. Need add -ComputedColumn option in the maintenance plan. Please go through the link and suggest.

 

Error:- [37] Database XXX: Check Data and Index Linkage…

[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’.

 

[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’.

    ** Execution Time: 0 hrs, 1 mins, 30 secs **

 

This problem occurs when the database contains a table that has an index on a computed column.

It’s a known bug from Microsoft.

http://support.microsoft.com/kb/902388

 

Optimization and integrity check job fails

Workaround:

  • Optimizations Job for DB Maintenance Plan ‘DB Maintenance Plan name
    For example, add the parameters to the command as shown in the following command:

EXECUTE master.dbo.xp_sqlmaint N’-S ServerName\InstanceName -PlanID <GUID> -WriteHistory -RebldIdx 10 -SupportComputedColumn’

  • Integrity Checks Job for DB Maintenance Plan ‘DB Maintenance Plan name
    For example, add the parameters to the command as shown in the following command:

EXECUTE master.dbo.xp_sqlmaint N’-S ServerName\InstanceName -PlanID <GUID> -WriteHistory -CkDB -SupportComputedColumn’

 

Advertisements
This entry was posted in Backup and Restore, Troubleshooting and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s