x

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

Rohit gravatar image

Rohit
11 1 1 1

(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:

SET ARITHABORT ON            
SET QUOTED_IDENTIFIER ON            

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

RickD gravatar image

RickD
1.7k 1 1 4

Be VERY careful when changing these settings at the server level. These are the settings which all .NET apps will use when connecting to the SQL Server (as well as ASP scripts, VB apps, PHP, etc) and changing these may break other stuff.
Oct 19, 2009 at 02:00 AM mrdenny
That is the reason for turning them back off at the end of the script as stated in the answer.
Oct 20, 2009 at 05:49 AM RickD
(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

Plucky gravatar image

Plucky
11

(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

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x474
x33

asked: Oct 14, 2009 at 06:40 AM

Seen: 1801 times

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