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'.
Answer by RickD ·
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.
Answer by Kristen ·
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.)
Answer by Plucky ·
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.