locking during index rebuild making db unavailable -- please help 
Author Message
 locking during index rebuild making db unavailable -- please help

Hi there,

We would like to have an expert advice about one tricky (at least for us)
problem.
We have a production database (>= 20 million records, 6Gb) with
more or less 2000 inserts everyday, and with massive updates
of the recently added records.
We scheduled the rebuild of the major indexes each week, but as there
are quite a lot of them, we encouter a complete lock of our tables during
this 2-3 hours job.
What should we do to ensure minimum locking duration?

Thanks in advance for any input.

--
Xavier TIZON
Support Engineer

TECHNIDATA
Grenoble, France



Sun, 22 Apr 2001 03:00:00 GMT
 locking during index rebuild making db unavailable -- please help

Without knowing the particulars of your situation, here are some
possibilities:  1) You might distribute the index rebuild--rebuild some on
Monday, some on Tuesday, etc.  You can manage this using the Task Manager in
Enterprise Manager.  It may be less disruptive to users if the rebuild is
scheduled for late at night.  2) You could increase the memory on your
server--the more memory you have the faster your indexes can be rebuilt.  3)
You can review your non-clustered indexes and DROP any that are not
needed--non-clustered indexes automatically are rebuilt when the clustered
index is rebuilt.
Quote:

> Hi there,

> We would like to have an expert advice about one tricky (at least for us)
> problem.
> We have a production database (>= 20 million records, 6Gb) with
> more or less 2000 inserts everyday, and with massive updates
> of the recently added records.
> We scheduled the rebuild of the major indexes each week, but as there
> are quite a lot of them, we encouter a complete lock of our tables during
> this 2-3 hours job.
> What should we do to ensure minimum locking duration?

> Thanks in advance for any input.

> --
> Xavier TIZON
> Support Engineer

> TECHNIDATA
> Grenoble, France



Sun, 22 Apr 2001 03:00:00 GMT
 locking during index rebuild making db unavailable -- please help
Hello again,

Thanks for your answers, Greg.

1) The rebuilds are already done at 3.00 AM, on a single index each day
2) My server has 196 Mo RAM : shouldn't this be enough?
3) I made some tests, and the 2 following statements seem to last exactly
the same
time :
dbcc dbreindex ('table_name', '', 90)
dbcc dbreindex ('table_name', 'table_index_clustered', 90)

Does anyone have any other idea about this?



Quote:
> Without knowing the particulars of your situation, here are some
> possibilities:  1) You might distribute the index rebuild--rebuild some
on
> Monday, some on Tuesday, etc.  You can manage this using the Task Manager
in
> Enterprise Manager.  It may be less disruptive to users if the rebuild is
> scheduled for late at night.  2) You could increase the memory on your
> server--the more memory you have the faster your indexes can be rebuilt.
3)
> You can review your non-clustered indexes and DROP any that are not
> needed--non-clustered indexes automatically are rebuilt when the
clustered
> index is rebuilt.


> > Hi there,

> > We would like to have an expert advice about one tricky (at least for
us)
> > problem.
> > We have a production database (>= 20 million records, 6Gb) with
> > more or less 2000 inserts everyday, and with massive updates
> > of the recently added records.
> > We scheduled the rebuild of the major indexes each week, but as there
> > are quite a lot of them, we encouter a complete lock of our tables
during
> > this 2-3 hours job.
> > What should we do to ensure minimum locking duration?

> > Thanks in advance for any input.



Mon, 23 Apr 2001 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. REbuilding Indexes during a fail over????

2. Please Help: VB4 Locks up during database access

3. table(s) locked during making of snapshot ?

4. TNS-12560 error during db creation - Please Help!

5. Index Rebuilds & Locks

6. Difference in ranking results from a full index rebuild verses an incremental rebuild

7. rebuild or not rebuild index file, when ?, how?

8. Please help - Error 1722 (RPC Server is unavailable)

9. Can't recalculate db size / can't rebuild index

10. 170 error in db maintenance rebuild index

11. Locking During Index Creation

12. Reprocessing cubes makes them unavailable


 
Powered by phpBB® Forum Software