is it possible to re-build index without logging?

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
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
>
|||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...
it's
to
> is
week,
>
|||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/pro.../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...
if
between.
> it's
insereted
> to
problem
> week,
>