Log shipping and DB reorgs

We're trying to set up log shipping in order to maintain a
backup/fallover database at a remote location. Everything
is looking pretty good, EXCEPT that whenever the nightly
database reorganization takes place on the primary
database, it apparently logs all the
various "transactions" resulting from the reorg activity.
It then ships this log to the secondary database, clogging
the pipeline something awful.
It seems intuitively odd that the reorg would result in
major logging. Be that as it may, can anybody think of a
way to avoid/circumvent/minimize this problem?
Thanks in advance for any advice.Ken,
I'm facing the same problem too, and AFAIK, there's no way to circumvent
this problem. I tried INDEXDEFRAG instead of DBREINDEX and it makes it even
worse sometimes.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Ken C" <anonymous@.discussions.microsoft.com> wrote in message
news:d5ae01c3ef42$eb8e08e0$a401280a@.phx.gbl...
> We're trying to set up log shipping in order to maintain a
> backup/fallover database at a remote location. Everything
> is looking pretty good, EXCEPT that whenever the nightly
> database reorganization takes place on the primary
> database, it apparently logs all the
> various "transactions" resulting from the reorg activity.
> It then ships this log to the secondary database, clogging
> the pipeline something awful.
> It seems intuitively odd that the reorg would result in
> major logging. Be that as it may, can anybody think of a
> way to avoid/circumvent/minimize this problem?
> Thanks in advance for any advice.|||Hi Carlos.
Would interleaving .diff backups between .tlogs in the shipping process
help? A few years back I rolled a custom log shipping solution to solve
other problems with log shipping, but I'm wonderinng if shipping .diff
backups interleaved with the .tlog backups would mitigate this problem,
assuming that the custom solution didn't transfer redundant .tlogs (the ones
with the dbcc commands)?
Regards,
Greg Linwood
SQL Server MVP
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:%23XCx%23Y07DHA.1672@.TK2MSFTNGP12.phx.gbl...
> Ken,
> I'm facing the same problem too, and AFAIK, there's no way to circumvent
> this problem. I tried INDEXDEFRAG instead of DBREINDEX and it makes it
even
> worse sometimes.
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 programming by Example
>
> "Ken C" <anonymous@.discussions.microsoft.com> wrote in message
> news:d5ae01c3ef42$eb8e08e0$a401280a@.phx.gbl...
>|||Greg,
That's a good idea, but you'd have to restore the whole database before
restoring each differential backup. This generates some overhead on the
destination server, and increases the recovery time.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:O4pYPe07DHA.2676@.TK2MSFTNGP10.phx.gbl...
> Hi Carlos.
> Would interleaving .diff backups between .tlogs in the shipping process
> help? A few years back I rolled a custom log shipping solution to solve
> other problems with log shipping, but I'm wonderinng if shipping .diff
> backups interleaved with the .tlog backups would mitigate this problem,
> assuming that the custom solution didn't transfer redundant .tlogs (the
ones
> with the dbcc commands)?
> Regards,
> Greg Linwood
> SQL Server MVP
> "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> news:%23XCx%23Y07DHA.1672@.TK2MSFTNGP12.phx.gbl...
> even
>|||I don't think so. He should be able to apply an appropriate differential
backup and then continue with log restores as long as he sticks to
NORECOVERY or STANDBY.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:OsmrIw07DHA.2656@.TK2MSFTNGP11.phx.gbl...
> Greg,
> That's a good idea, but you'd have to restore the whole database before
> restoring each differential backup. This generates some overhead on the
> destination server, and increases the recovery time.
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 programming by Example
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:O4pYPe07DHA.2676@.TK2MSFTNGP10.phx.gbl...
> ones
circumvent
>|||I know, but before applying a diff backup, you have to restore the full
database backup first.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eStNI007DHA.2300@.TK2MSFTNGP10.phx.gbl...
> I don't think so. He should be able to apply an appropriate differential
> backup and then continue with log restores as long as he sticks to
> NORECOVERY or STANDBY.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> news:OsmrIw07DHA.2656@.TK2MSFTNGP11.phx.gbl...
process
solve
problem,
(the
> circumvent
it
>|||Yep.
The solution I designed was for sql7, & it basically used control tables on
the server and the target, allowed .fbak, .diff & .tlogs to occur on
whatever schedule, then restored headeronly to get lastlsn & only shipped /
restored the "best" option to the target (a .diff if more recent lsn &
smaller than .tlogs).
Although a bit complex, the design goal for this client was to support
shipping to a much smaller box for ad-hoc reporting (a common goal).. The
client I did this for had implemented an e-commerce solution called
"Interworld" that had a db design which used manual identity generation
tables (update idtable.. select max()+1.. scenario) with ga-zillions of
updates yet a very small table space footprint. So it was a similar problem
to your dbcc problem - lots of .tlog updates to a relative to the db size.
These guys were also doing lots of nightly dbcc & I as I recall we tested /
confirmed a benefit in that area too so I think it might help you..
Regards,
Greg Linwood
SQL Server MVP
"Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eStNI007DHA.2300@.TK2MSFTNGP10.phx.gbl...
> I don't think so. He should be able to apply an appropriate differential
> backup and then continue with log restores as long as he sticks to
> NORECOVERY or STANDBY.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> news:OsmrIw07DHA.2656@.TK2MSFTNGP11.phx.gbl...
process
solve
problem,
(the
> circumvent
it
>|||Just to clarify - this solution did operate within weekly full db backups..
but you could do something similar within *whatever* .fbak window - monthly,
quarterly etc..
Regards,
Greg Linwood
SQL Server MVP
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:%23ZtuO307DHA.3880@.tk2msftngp13.phx.gbl...
> I know, but before applying a diff backup, you have to restore the full
> database backup first.
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 programming by Example
>
> "Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:eStNI007DHA.2300@.TK2MSFTNGP10.phx.gbl...
differential
before
the
> process
> solve
.diff
> problem,
> (the
makes
> it
>|||Afetr reading this thread, a somewhat othorgonal solution is to consider
cutting down the amount of reorging you're doing every night. Are you
reorging just for the sake of it, or do you have empirical evidence that
increasing fragmentation is slowing down your range scans?
Check out the whitepaper below for more details:
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ken C" <anonymous@.discussions.microsoft.com> wrote in message
news:d5ae01c3ef42$eb8e08e0$a401280a@.phx.gbl...
> We're trying to set up log shipping in order to maintain a
> backup/fallover database at a remote location. Everything
> is looking pretty good, EXCEPT that whenever the nightly
> database reorganization takes place on the primary
> database, it apparently logs all the
> various "transactions" resulting from the reorg activity.
> It then ships this log to the secondary database, clogging
> the pipeline something awful.
> It seems intuitively odd that the reorg would result in
> major logging. Be that as it may, can anybody think of a
> way to avoid/circumvent/minimize this problem?
> Thanks in advance for any advice.|||Each time you rebuild the index all pages that are changed end up in the tra
nsaction log that gets shipped to the other SQL Instance. The question is,
"do you need to rebuild all these indexes all the time?" Have you checked t
he rate at which EACH index
becomes fragmented? It seems that you probably need multiple reindex jobs t
hat run at different frequencies (ie Weekly those indexes that fragment quic
kly and seriously impact performance, and Monthly/Bimonthly for those indexe
s that have less effect on
query execution time).