DB Optimization

SQL 7.0
I scheduled a Optimization job to run weekly once and its taking around 5
hrs .During this time users are getting locked.
What are all the best options to handle this '
We are not able to get a continuous 5 hrs down time for application '
Thx
ShDo you mean index rebuilds?
Index rebuilds acquire locks on the table (exclusive for clustered =indexes and shared for non-clustered); they do require downtime.
SQL Server 7.0 doesn't have an online index defrag option such as the =one provided by SQL Server 2000 (DBCC INDEXDEFRAG).
-- BG, SQL Server MVP
Solid Quality Learning
www.solidqualitylearning.com
"Shamim" <shamim.abdul@.railamerica.com> wrote in message =news:OsxzOzSVDHA.1832@.TK2MSFTNGP09.phx.gbl...
> SQL 7.0
> > I scheduled a Optimization job to run weekly once and its taking =around 5
> hrs .During this time users are getting locked.
> What are all the best options to handle this '
> > We are not able to get a continuous 5 hrs down time for application '
> > Thx
> Sh
> >|||Itzik
An alternative is to use the DBCC command SHOWCONTIG to
get an idea of the fragmentation of your files. It is very
likely that they fragment at different rates. When you get
a feel for how quickly your different tables fragment you
may be able to schedule your index rebuilds in a way that
fits into your available window.
Regards
John|||Thanks Itzik for the reply.
Just wanna know , how a production critical environment handle this issue'
Is it like, transfering application to a hot backup , optimize production db
, restore the downtime transaction log and connect back.
Thx
Sh
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:eVN192SVDHA.1928@.TK2MSFTNGP12.phx.gbl...
Do you mean index rebuilds?
Index rebuilds acquire locks on the table (exclusive for clustered indexes
and shared for non-clustered); they do require downtime.
SQL Server 7.0 doesn't have an online index defrag option such as the one
provided by SQL Server 2000 (DBCC INDEXDEFRAG).
--
BG, SQL Server MVP
Solid Quality Learning
www.solidqualitylearning.com
"Shamim" <shamim.abdul@.railamerica.com> wrote in message
news:OsxzOzSVDHA.1832@.TK2MSFTNGP09.phx.gbl...
> SQL 7.0
> I scheduled a Optimization job to run weekly once and its taking around 5
> hrs .During this time users are getting locked.
> What are all the best options to handle this '
> We are not able to get a continuous 5 hrs down time for application '
> Thx
> Sh
>