Log shipping question

28 May 2005 5:25 AM

Bill Wang

My company is embarking on a DRP project where we will move our production
servers to a less geographically insecure area. We'll need to perform log
shipping back to our DRP site.

I know that SQL Server logs index rebuilds or index defragmentation and that
we perform rebuilds regularly so I have a few questions if anyone can help:

1.. Can we stop index builds from being logged?
2.. If we cannot stop index logging, are there any different practices we
can follow to do index maintenance that will not require logging (e.g.
defrag)
Does anyone have any experience of this?

If we were to ship index rebuilds or defragmentation, we'll be shipping
gigabytes of unnecessary data, but apparently SQL Server will not let this
be switched off?

Thanks.

Bill

28 May 2005 8:49 AM

Tibor Karaszi

If you don't log operations, the forth coming log backup would not be useful. You cannot eat the
cake and have it. :-)

You could look into bulk logged recovery mode, but a log backup in this mode will include data (not
log records) modified by minimally logged operations. I doubt you will gain much, if anything.

Your best bet is to only rebuild if you need to. A start is to use the code in Books Online, DBCC
SHOWCONTIG which only do rebuild if frag level is > a certain level. You can customize this further
and rebuild different indexes at different times.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quoteHide quote

"Bill Wang" <wdw2130***@hotmail.com> wrote in message
news:9fTle.28354$Ot6.1561166@news20.bellglobal.com...
> My company is embarking on a DRP project where we will move our production
> servers to a less geographically insecure area. We'll need to perform log
> shipping back to our DRP site.
>
> I know that SQL Server logs index rebuilds or index defragmentation and that
> we perform rebuilds regularly so I have a few questions if anyone can help:
>
> 1.. Can we stop index builds from being logged?
> 2.. If we cannot stop index logging, are there any different practices we
> can follow to do index maintenance that will not require logging (e.g.
> defrag)
> Does anyone have any experience of this?
>
> If we were to ship index rebuilds or defragmentation, we'll be shipping
> gigabytes of unnecessary data, but apparently SQL Server will not let this
> be switched off?
>
> Thanks.
>
>
>
> Bill
>
>