May 19, 2003 at 2:20 am
I have created a maintenance plan using the maintenance plan wizard in SQL 2K. I use the job to perform backups and rebuild the indexes within a database. I seem to be having some problems with the optimisation job created by the wizard. This job worked fine the first it ran but the last few weeks it has failed.
I checked the log file produced by the optimisation job and it shows that it is rebuilding indexes for 6 tables and then there is an error message:
[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'.
A quick browse on the Internet revealed a MS KB article on the problem (301292):
http://support.microsoft.com/?kbid=301292
This states that the problem is due to ceratin SET options being required to run the DBCC job when a table in the database has an index on a computed column. I do have several computed columns within the database but none of these are indexed.
The KB article suggests including a couple of set options in the batch which runs the DBCC job so I modified the job step which starts the maintenance plan to be:
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
EXECUTE master.dbo.xp_sqlmaint ....
And still I have the same results.
Also I have a job which backs up the msdb and master databases each evening. Although these DB's are tiny (both < 20Mb) the backup of each one is around 900Mb. Could this be connected to the optimisation job failing?
Has anyone seen a problem like this before and can anyone help?
Thanks in advance
May 19, 2003 at 8:37 pm
There is another thread discussing the same topic. Basically, you'll need to either:
1) Drop the index on any of the computed columns in your database(s).
2) Stop using maintenance plans. Setup the equivalent commands in an Agent job yourself and include the correct settings as detailed in KB 301292.
3) Wait for Microsoft's patch to correct the hard-coded values within SQLMAINT.EXE
David R Buckingham, MCDBA,MCSA,MCP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply