Re: is it possible to re-build index without logging?

From: Paul S Randal [MS] (prandal_at_online.microsoft.com)
Date: 04/02/04 

Date: Fri, 2 Apr 2004 13:45:59 -0800

Checkout DBCC INDEXDEFRAG, which does online defragmentation. You can look
in BOL for details. There's also an excellent whitepaper at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
which will give you a bunch of details, hints, and explanations - including
working out whether you even need to bother getting rid of fragmentation,
based on your workload.

Regards. 

-- 
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Lin" <lins@nospam.portptld.com> wrote in message
news:eDkxfqPGEHA.3324@TK2MSFTNGP09.phx.gbl...
> as i said, the database is write intensive. so simple recovery mode might
be
> too risky. is there a way to run a scheduled re-index job as a non-logged
> operation?
>
> thank you so much for the help.
>
> steve
>
> "Andrew J. Kelly" <sqlmvpnoooospam@shadhawk.com> wrote in message
> news:ujUS9aFGEHA.2876@TK2MSFTNGP09.phx.gbl...
> > You can try changing the recovery mode to Bulk Logged or Simple and see
if
> > that helps.  Do only a few tables at a time and backup the log in
between.
> >
> >
> > --
> > Andrew J. Kelly  SQL MVP
> >
> >
> > "Steve Lin" <lins@nospam.portptld.com> wrote in message
> > news:#otAPXEGEHA.3288@TK2MSFTNGP12.phx.gbl...
> > > nt5, sql2k
> > >
> > >
> > >
> > > i have a vendor application on one of my sql servers.
> > >
> > > this application has many tables, and each tables have many indexes.
> it's
> > > very write intensive (because when a record is inserted, it's
insereted
> to
> > > many many other tables, and that behavior can't be changed). the
problem
> > is
> > > page split happened often, but when i tried to reindex tables once a
> week,
> > > the log file run out of space.
> > >
> > >
> > >
> > > can anyone offers some good solutions to my problem?
> > >
> > > thank you!!
> > >
> > >
> > >
> > > Steve
> > >
> > >
> >
> >
>
>

Relevant Pages