Microsoft KB Archive/902388

= Event ID: 208 may be logged, and a &quot;DBCC failed&quot; 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: 902388

Article Last Modified on 11/2/2007

-

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:

Time:

User: NA

Computer:

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

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 \  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 \  represents the server name and the instance name. If you are using a default instance of SQL Server 2000, you can use the –S  parameter, or you can safely ignore the parameter.  Optimizations Job for DB Maintenance Plan ' '

For example, add the parameters to the command as shown in the following command:

EXECUTE master.dbo.xp_sqlmaint N'-S \  -PlanID   -WriteHistory -RebldIdx 10 -SupportComputedColumn'

 Integrity Checks Job for DB Maintenance Plan ' '

For example, add the parameters to the command as shown in the following command:

EXECUTE master.dbo.xp_sqlmaint N'-S \  -PlanID   -WriteHistory -CkDB -SupportComputedColumn'



Notes
 * In this command,  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 &quot;Applies to&quot; section.

