|
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'.
(comments are locked)
|
|
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. 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 '09 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 '09 at 05:49 AM
RickD
(comments are locked)
|
|
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
(comments are locked)
|
|
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.)
(comments are locked)
|
