I have a pretty standard log shipping arrangement created with the Log
Shipping wizard against an existing database on the standby server. All the
jobs were created successfully and run on the correct schedule. However,
the copy job on the standby server doesn't work. It shows that it ran
successfully, but the files just aren't copied to the standby server's
directory, and so are not restored. After manually copying the transaction
log files to the standby server, the restore job will work. However, the
next log in sequence will not copy over.
The maintenance plan history shows no errors.
Both servers are running Windows 2000 servers and SQL Server 2000 Enterprise
SP3a. The link is a 45 Mbit leased line.
What could be the problem?
Thanks,
RS
RS,
usually this is a problem with the paths - the wizard is not very intuitive
and it's quite easy to set them incorrectly. On the screen that says
'Specify the Transaction Log Share', you must identify the file share on the
primary server and when setting up the destination, the name is a local
pathname, not a file share. If this is incorrect, there will be no errors
and the monitor will show continually the first setup file as being
transferred. So, initially check that you have a share on the primary server
and the logs are getting there.
HTH,
Paul Ibison
sql
Showing posts with label pretty. Show all posts
Showing posts with label pretty. Show all posts
Wednesday, March 21, 2012
Friday, February 24, 2012
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).
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).
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).
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).
Subscribe to:
Posts (Atom)