Hi Garth,
I have exactly the same problem as yours and the only way that I managed to
get around it is to backup the tables that is giving me problem, drop and
re-create/re-populate those tables that is giving me that error in the
maintenance plan. I tried a truncate of the tables but the foreign keys
relationships are giving me more error messages and as per the Microsoft
Support Knowledgebase when I do a search of this problem, it seems to be a
known bug that has no fix but to re-create the tables. This is one of the
option, option 1. I manage to re-produce the problem by backing up and
restoring that database into a local PC and run a similar maintenance plan.
It gives me the same problem and that is where I tried the suggestion to
backup and drop/re-create my tables and re-run the maintenance plan, it
works out alright after that.
Option 2 is to exclude that database from the maintenance plan, run
SQL-Profiler to get the DBCC commands that is being run for this database
during the maintenance plan, create a SQL script with these DBCC commands
and then schedule a job for it to run against this database. If you check
through SQL-Profiler, I think last time I checked it is actually doing DBCC
REINDEX, but because the QUOTED_IDENTIFIER and ARITHABORT settings conflicts
with when Scheduler's settings are when it runs the plan, it fails. Check
http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans
.asp and http://kerblog.com/earlyedition/archive/2004/11/25/247.aspx
In short, if you do not want to exclude this database from the maintenance
plan, then you have to do option 1, drop and re-create the tables that is
giving you problem. But if you are more than happy to exclude this database
from the maintenance plan and just create another script that will do
Integrity Checks solely for this database that is giving you problem, then
do option 2 as per the second paragraph where you do not need to drop and
re-create the tables. Please note that if you do choose to do option 2, you
need to do the SET QUOTED_IDENTIFIER and SET ARITHABORT correctly from
within your scripts, something you couldn't do when this database is
included in the maintenance plan.
Hope this helps ...
-----Original Message-----
From: Garth Joubert
[mailto:mssqldba-ezmlmshield-x84451588.[Email address protected]
Sent: Thursday, 28 April 2005 11:19 a.m.
To: LazyDBA Discussion
Subject: Maintenance Plan error message "'QUOTED_IDENTIFIER, ARITHABORT"
Hi All,
I have a scheduled Maintenance Plan that errors with the following
message:
"[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the
following SET options have incorrect settings: 'QUOTED_IDENTIFIER,
ARITHABORT'.
It always errors on the same DataBase. The error occurs on this particular
database when trying to "Rebuild Indexes" and "Integrity Checks"
We are running SQl Server 2000 Standard Edition with SP3 (8.00.813) under
Windows Server 2003.
Can anyone help please.
Thanks,
Garth Joubert
DataBase Administrator
Phone (DDI): 07 574 4864
Facsimile: 07 574 4863
Email: garth.[Email address protected]
TrustPower Limited
Private Bag 12023
Tauranga
New Zealand
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.4 - Release Date: 27/04/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.4 - Release Date: 27/04/2005
MS Sql Server LazyDBA home page