December 21, 2004 at 6:00 am
What is the syntax for running "Reorganize data and index pages" in QA.
Is it DBCC DBREINDEX(Eachtable)?
Anything else?
Is there a way that I can run DBREINDEX for each table in a database without having to specify tables individually.
Reason for the question is that I am getting the following error message when the nightly Maintenance Plan rebuilds the database indexes:
[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'.
When I uncheck "Reorganize data and index pages" the maintenance plan runs successfully.
I believe that I need to set up a new job something like following:
SET ARITHABORT OFF
SET QUOTED_IDENTIFIER OFF
DBCC DBREINDEX(Eachtable)
I am also wondering whether it would be better to update statistics on each table every night and just rebuild indexes once a week?
December 21, 2004 at 7:06 am
See, if this helps:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Have you already searched the script section here? I bet you'll find a whole bunch of maintenance scripts there.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 21, 2004 at 8:01 am
Frank, thanks for the link - will certainly help my understanding.
Also looked through scripts and found two which seemed to apply:
DBCC Reindex all tables and CreateDBReindex (see links below). The former took 10:39 and the latter 6:57 this compared to the complete optimisation job which only took about 2 minutes to run.
Do these scripts do a similar thing to "Reorganize data and index pages" the maintenance plan?
I guess that currently the duration is not too much of an issue because the database is currently only being used in Europe and therefore any performance impact overnight is not an issue. In this case I guess that I might as well set up a job using one of these scripts to reindex every night.
The scripts I tried:
http://www.sqlservercentral.com/scripts/contributions/139.asp
http://www.sqlservercentral.com/scripts/contributions/935.asp
November 1, 2005 at 10:38 am
No one has really answered the question: What does "Reorganize data and index pages" do under the hood (DB Maint Plan, Optimizations tab - first checkbox)? Is it just running DBCC DbReindex? We've been running this optimization, but it doesn't take very long, and I'm wondering if it's really running DBCC DBREINDEX on all tables for all databses.
Thanks,
Matthew
November 30, 2005 at 10:57 am
I know this is way after the fact but I have also been looking for more info about what is going on under the hood in the maintenance plans.
I just wanted to throw out the following site that has a pretty good explanation of what is happening in the maintenance plan wizard.
http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp
Good luck!
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply