win2k sp2/sql2k sp2

Last weekend I attempted to run maintenance to "reorganize data and index
files". It failed because it filled up the tran log. My db is 50GB and my
log was 30GB.

I'm thinking about switching the recovery mode to simple, running the
maintenance task, then switching back to full recovery. I was thinking this
would be what I'd do:
- ALTER DATABASE LIVE
SET RECOVERY SIMPLE
- Run Maintenance
- ALTER DATABASE LIVE
SET RECOVERY FULL

I'm not worried at all about losing any transactions during this period as
it's a quiet period. That said, is there any danger in doing this? And
maybe more importantly, would it allow my "reorganize data and index pages"
task to finish?

Thanks, Andre

Report this post to a moderator

That should be fine. Just do a FULL backup first.

--
Andrew J. Kelly SQL MVP
Targitinteractive, Inc.



"Andre" <AndreGetsEnoughSPAM@nospam.com> wrote in message
news:emqPbJsHCHA.404@tkmsftngp13...
> win2k sp2/sql2k sp2
>
> Last weekend I attempted to run maintenance to "reorganize data and index
> files". It failed because it filled up the tran log. My db is 50GB and
my
> log was 30GB.
>
> I'm thinking about switching the recovery mode to simple, running the
> maintenance task, then switching back to full recovery. I was thinking
this
> would be what I'd do:
> - ALTER DATABASE LIVE
> SET RECOVERY SIMPLE
> - Run Maintenance
> - ALTER DATABASE LIVE
> SET RECOVERY FULL
>
> I'm not worried at all about losing any transactions during this period as
> it's a quiet period. That said, is there any danger in doing this? And
> maybe more importantly, would it allow my "reorganize data and index
pages"
> task to finish?
>
> Thanks, Andre
>
>

Report this post to a moderator

Thanks.


"Andrew J. Kelly" <akelly@targitinteractive.com> wrote in message
news:eALB64sHCHA.216@tkmsftngp10...
> That should be fine. Just do a FULL backup first.
>
> --
> Andrew J. Kelly SQL MVP
> Targitinteractive, Inc.
>
>
>
> "Andre" <AndreGetsEnoughSPAM@nospam.com> wrote in message
> news:emqPbJsHCHA.404@tkmsftngp13...
> > win2k sp2/sql2k sp2
> >
> > Last weekend I attempted to run maintenance to "reorganize data and
index

> > files". It failed because it filled up the tran log. My db is 50GB and
> my
> > log was 30GB.
> >
> > I'm thinking about switching the recovery mode to simple, running the
> > maintenance task, then switching back to full recovery. I was thinking
> this
> > would be what I'd do:
> > - ALTER DATABASE LIVE
> > SET RECOVERY SIMPLE
> > - Run Maintenance
> > - ALTER DATABASE LIVE
> > SET RECOVERY FULL
> >
> > I'm not worried at all about losing any transactions during this period
as
> > it's a quiet period. That said, is there any danger in doing this? And
> > maybe more importantly, would it allow my "reorganize data and index
> pages"
> > task to finish?
> >
> > Thanks, Andre
> >
> >
>
>

Report this post to a moderator