Database Optimization Maintenance error

I have a SQL Server 2000 database that is approximately 80 GB in size.

I set up the SQL Server maintenance plan with the SQL Server Enterprise Manager. I created the Optimization job and only selected the 'Reorganize data and index page' and 'Change free space per page percentage to 10%' parameters.

I received the error listed below from the Optimization job.

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS failed

because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.

more ▼

asked Oct 14, 2009 at 06:40 AM in Default

avatar image

11 1 1 3

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

These are settings at the server level, right click on server and go to properties. You will need to set these to the following:


The best way to do this is to have your own script for updating the statistics, you can even get this from the main plan by scripting it out if you don't want to write it yourself. Add the above SET statements to the top of the script and add some at the end to turn them off (as your database has been working up until now without them), schedule the script and voila.

more ▼

answered Oct 14, 2009 at 09:10 AM

avatar image

1.7k 2 3 6

(comments are locked)
10|1200 characters needed characters left

Add "-SupportComputedColumn" to the end of the command in the job created by the maint plan, if you have any computed columns in the DB being optimised.

See http://support.microsoft.com/default.aspx?scid=kb;en-us;902388

more ▼

answered Feb 09, 2010 at 11:09 AM

avatar image


(comments are locked)
10|1200 characters needed characters left

I don't think the out-of-the-box Maintenance on SQL2000 is "sharp enough" for an 80GB database.

For our SQL2000 databases we have very complex, in-house created, processes that decide whether the amount of fragmentation in an index required rebuilding, or not, and whether the index is small enough that we can use Index Rebuild, and if not we use Index Defrag (which does not disrupt concurrent use).

The out-of-the-box maintenance Wizard in SQL2005 is much MUCH better (and there are more choices for maintenance which ensure that concurrent use does not cause timeouts etc.)

more ▼

answered Oct 16, 2009 at 04:19 PM

avatar image

Kristen ♦
2.2k 7 11 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 14, 2009 at 06:40 AM

Seen: 2047 times

Last Updated: Oct 14, 2009 at 09:09 AM

Copyright 2018 Redgate Software. Privacy Policy