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...
> > 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.
>|||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...
> > 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.
> >
> >
>|||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...
> > 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...
> > > > 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.
> > >
> > >
> >
> >
>|||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...
> > 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...
> > > > 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.
> > > >
> > > >
> > >
> > >
> >
> >
>|||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...
> > 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...
> > > > 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.
> > > >
> > > >
> > >
> > >
> >
> >
>|||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...
> > 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...
> > > > 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...
> > > > > > 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.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||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/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/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 transaction 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 the rate at which EACH index becomes fragmented? It seems that you probably need multiple reindex jobs that run at different frequencies (ie Weekly those indexes that fragment quickly and seriously impact performance, and Monthly/Bimonthly for those indexes that have less effect on query execution time).|||Look in BOL under DBCC SHOWCONTIG for a noce template procedure that can
drive index defragmentation only when the fragmentation level reaches a
threshold you determine. After a short time, you can dramatically reduce
the amount of data pushed through log shipping by only defragging when
necessary.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Stephen Strong" <anonymous@.discussions.microsoft.com> wrote in message
news:4C8E3875-0731-4F49-9D8C-8652CB631D0B@.microsoft.com...
> Each time you rebuild the index all pages that are changed end up in the
transaction 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 the rate at which EACH index becomes fragmented? It seems that you
probably need multiple reindex jobs that run at different frequencies (ie
Weekly those indexes that fragment quickly and seriously impact performance,
and Monthly/Bimonthly for those indexes that have less effect on query
execution time).