reindex maintenance plan

We have created a maitenance plan that reindex all our tables. Usually it
works, ocassionaly it halts the entire system moments after starting. I
assume this is a reindex issue and not a maintenance plan issue. Any
suggestions on where I can being my search for a fix?
TIA
Paulnot exactly sure what you mean by "Halts Entire System". However, the
maintenance plan wizard uses DBCC DBReindex for index maintenance. DBReindex
places exclusive table locks on tables being defragged.
You may want to consider using your own custom index maintenance routines
and implementing index maintenance via "DBCC IndexDefrag" instead.
cheers
Greg Jackson
Portland, OR|||The MP uses DBCC DBREINDEX which will attempt to use all available
processors to do the work in as short a time as possible. It will use 100%
or close to that amount of all the processors for some period of time
throughout the process. While it is reindexing a table that particular table
is off line for the duration of the reindex process on that table. If the
use of all the processors is too much of a load you can set thee MAXDOP at
the server level to limit how many are used by any one source. Of coarse
this type of activity should be done when there is little load on the
server.
--
Andrew J. Kelly SQL MVP
"itchicago" <itchicago@.discussions.microsoft.com> wrote in message
news:05750327-FB10-4E25-82E6-650381C0BCDC@.microsoft.com...
> We have created a maitenance plan that reindex all our tables. Usually it
> works, ocassionaly it halts the entire system moments after starting. I
> assume this is a reindex issue and not a maintenance plan issue. Any
> suggestions on where I can being my search for a fix?
> TIA
> Paul|||To add to Greg's reply, you should read the whitepaper below which explains
when and how to get rid of index fragmentation. Usually, rebuilding all
indexes in a database is a wasted operation and you can be much more
selective. DBREINDEX will take a X lock (i.e. unavailable for read/write) on
a table if the clustered index is being rebuilt, but only an S lock
(unavailable for write) on the table if a non-clustered index is being
rebuilt. You can use Example E that I wrote for BOL for DBCC SHOWCONTIG as a
good starting point for a custom defrag job.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:#qDYI9SDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> not exactly sure what you mean by "Halts Entire System". However, the
> maintenance plan wizard uses DBCC DBReindex for index maintenance.
DBReindex
> places exclusive table locks on tables being defragged.
> You may want to consider using your own custom index maintenance routines
> and implementing index maintenance via "DBCC IndexDefrag" instead.
>
> cheers
> Greg Jackson
> Portland, OR
>