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 (902388)


The information in this article applies to:

  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Personal Edition

SYMPTOMS

When you try to use the Database Maintenance Plan Wizard to create a maintenance plan in Microsoft SQL Server 2000, an event that is similar to the following event may be logged in the Application log:Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: date
Time: time
User: NA
Computer: servername
Description:
SQL Server Scheduled Job 'Optimizations Job for DB Maintenance Plan 'DB Maintenance Plan3'' (0x385AEFD8A5406044BF854C7011E501AD) - Status: Failed - Invoked on: 2005-06-23 16:05:37 - Message: The job failed. The Job was invoked by User CORPA\Administrator. The last step to run was step 1 (Step 1).
For more information, see Help and Support Center at http://support.microsoft.com.Additionally, the following error message is logged in the Database Maintenance Plan History entry:

[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'.
This problem occurs when the database contains a table that has an index on a computed column.

CAUSE

This problem occurs because a job is created after you create a maintenance plan by using the Database Maintenance Plan Wizard. This job has the following name:

Optimizations Job for DB Maintenance Plan 'DB Maintenance Plan name'

This job contains a dbo.xp_sqlmaint stored procedure. This stored procedure calls the Sqlmaint utility. The Sqlmaint utility runs DBCC checks, backs up a database and the database transaction log, updates statistics, and rebuilds indexes. Then, the Sqlmaint utility runs the DBCC CHECKDB statement, the DBCC DBREINDEX statement, and the DBCC CHECKTABLE statement. These statements require that the QUOTED_IDENTIFIER SET option is set to ON. By default, the Database Maintenance Plan Wizard sets the QUOTED_IDENTIFIER SET option to ON when the Database Maintenance Plan Wizard creates this job.

Additionally, when the QUOTED_IDENTIFIER SET option is set to ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. However, the SQL statement in the command that is included in the first step of this job only uses single quotation marks. The identifier in this SQL statement is delimited by single quotation marks instead of by double quotation marks. Therefore, this problem occurs.

Note By default, the QUOTED_IDENTIFIER SET option is set to OFF. When the QUOTED_IDENTIFIER SET option is set to OFF, identifiers cannot be delimited by double quotation marks. When the QUOTED_IDENTIFIER SET option is set to OFF, identifiers must follow all Transact-SQL rules for identifiers.

WORKAROUND

To work around this problem, add the -S ServerName\InstanceName parameter and the -SupportComputedColumn parameter to the command that is included in the first step of the following jobs.

Note If you are using a named instance, the placeholder ServerName\InstanceName represents the server name and the instance name. If you are using a default instance of SQL Server 2000, you can use the -S ServerName parameter, or you can safely ignore the parameter.
  • 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'

Notes
  • In this command, <GUID> is a placeholder for the PlanID plan identifier.
  • If you change the configurations in the Properties dialog box of the maintenance plan, the EXECUTE master.dbo.xp_sqlmaint command that is included in the first step of this job will be overwritten.
  • The -SupportComputedColumn parameter is a new feature that was introduced in SQL Server 2000 Service Pack 4 (SP4). Therefore, this workaround requires that you have SQL Server 2000 SP4 installed.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

301292 SET OPTION considerations when running DBCC with indexes on computed columns

Modification Type:MajorLast Reviewed:9/22/2006
Keywords:kbtshoot kberrmsg kbprb KB902388 kbAudDeveloper kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.