optimization job failing after 6 months

[Richey]

Hi

I setup a Maintenance Plan 6 months ago for optimization, data integrity and complete backup - it has worked fine until this morning ? the optimzation is failing on two database called cccinternet and training48.............this is the error message - error number 1934 for cccinternet

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

any ideas
regards
richey

 

[hmckillop]

Has the database options been changed on the system recently?
has the schema changed or new indexed views been added recently?
If you use this proc it will show all objects which have the QUOTED_Identifier or ansi nulls options on.
You will need to check the actual database setting for the arithabort (or the connection you use for backing up the database)

Declare @v_temp Table (Objectid bigint, objectname varchar(100), xtype varchar(3))
declare @v_temp_count int, @v_i int

insert into @v_temp 
select id, name, xtype from sysobjects where xtype in ('U', 'P', 'FN') 
order by xtype, name

select @v_temp_count= count(Objectid), @v_i = 0 from @v_temp


	select 
		tmp.ObjectName, 
		objectproperty (Object_ID(tmp.objectname), 'IsAnsiNullsOn') as 'isansinullsOn', 
		objectproperty (Object_ID(tmp.objectname), 'IsQuotedIdentOn') as 'isQuotedIdentifiersOn'
	FROM 
		@v_temp tmp
	where objectproperty (Object_ID(tmp.objectname), 'IsAnsiNullsOn') = 0 or objectproperty (Object_ID(tmp.objectname), 'IsQuotedIdentOn') = 0

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Richey]

thanks

the only thing that has changed is someone added a job to run, 04:30, which clashed with the optimization time ?
however I'm running this job now so the timing isn't an issue ?
the quoted identifiers seem ok - I'm not sure what you mean when you say
the actual database setting for the arithabort
how do i check for that ?

 

[hmckillop]

For arithabort, in enterprise manager, select the server, right click and select properties, then the connections tab and then check if the arithabort option is on.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[colinbell]

thanks

checked that - no options are checked i.e. on

 

[hmckillop]

Do you have either an index on a computed column, or SQL Server has created statistics
on a computed column.
In those situations, whenever you need to rebuild such an index or statistics, the
connection need special SET options defined. For some reason, Maint wizard doesn't set these. You
could try to drop the statistics (DROP STATISTICS), chances are that they will re-added. You could
tell SQL Server to not create statistics, but the issue might pop up somewhere else. To be safe,
perform the DBCC DBREINDEX commands from your own SQL Server Agent job and make sure to set the SET
commands as they need to be set.
You are probably getting the error on index rebuild time.

Try specifically setting your connection properties in your job - i.e. execute these in each of the steps of your job

SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNING ON
SET NUMERIC_ROUNDABORT OFF

"I'm living so far beyond my income that we may almost be said to be living apart

 

[colinbell]

thanks

I've discovered that someone had restored a table which now has a computed column on it !

sorry about that
regards

 

[hmckillop]

No problem, i see though have a bit of a split-personality - two logins responding to the same problem

"I'm living so far beyond my income that we may almost be said to be living apart