We are currently setting up log shipping using SQL 2000. We have a local
server on a 15 minute delay and a remote server on a 3 hour delay. I am
curious as to the process of recovering the logs in the event of a failover.
Has somebody ever done a script, or know of a way to automate the process of
"catching up" the application of the log files in the event of a failover?
Specifically I am looking to automate the process of recognizing the last
log applied and then applying the subsequent logs required to bring the DB
back online.
Any help would be appreciated.
Thanks
Ron
Please don't multi-post.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ron VZ" <rvanzant@.premierbankcard.com> wrote in message
news:uYUCrvlQEHA.368@.TK2MSFTNGP09.phx.gbl...
> We are currently setting up log shipping using SQL 2000. We have a local
> server on a 15 minute delay and a remote server on a 3 hour delay. I am
> curious as to the process of recovering the logs in the event of a
failover.
> Has somebody ever done a script, or know of a way to automate the process
of
> "catching up" the application of the log files in the event of a failover?
> Specifically I am looking to automate the process of recognizing the last
> log applied and then applying the subsequent logs required to bring the DB
> back online.
>
> Any help would be appreciated.
>
> Thanks
>
> Ron
>
Showing posts with label remote. Show all posts
Showing posts with label remote. Show all posts
Wednesday, March 28, 2012
Log shipping script
We are currently setting up log shipping using SQL 2000. We have a local
server on a 15 minute delay and a remote server on a 3 hour delay. I am
curious as to the process of recovering the logs in the event of a failover.
Has somebody ever done a script, or know of a way to automate the process of
"catching up" the application of the log files in the event of a failover?
Specifically I am looking to automate the process of recognizing the last
log applied and then applying the subsequent logs required to bring the DB
back online.
Any help would be appreciated.
Thanks
RonPlease don't multi-post.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ron VZ" <rvanzant@.premierbankcard.com> wrote in message
news:uYUCrvlQEHA.368@.TK2MSFTNGP09.phx.gbl...
> We are currently setting up log shipping using SQL 2000. We have a local
> server on a 15 minute delay and a remote server on a 3 hour delay. I am
> curious as to the process of recovering the logs in the event of a
failover.
> Has somebody ever done a script, or know of a way to automate the process
of
> "catching up" the application of the log files in the event of a failover?
> Specifically I am looking to automate the process of recognizing the last
> log applied and then applying the subsequent logs required to bring the DB
> back online.
>
> Any help would be appreciated.
>
> Thanks
>
> Ron
>
server on a 15 minute delay and a remote server on a 3 hour delay. I am
curious as to the process of recovering the logs in the event of a failover.
Has somebody ever done a script, or know of a way to automate the process of
"catching up" the application of the log files in the event of a failover?
Specifically I am looking to automate the process of recognizing the last
log applied and then applying the subsequent logs required to bring the DB
back online.
Any help would be appreciated.
Thanks
RonPlease don't multi-post.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ron VZ" <rvanzant@.premierbankcard.com> wrote in message
news:uYUCrvlQEHA.368@.TK2MSFTNGP09.phx.gbl...
> We are currently setting up log shipping using SQL 2000. We have a local
> server on a 15 minute delay and a remote server on a 3 hour delay. I am
> curious as to the process of recovering the logs in the event of a
failover.
> Has somebody ever done a script, or know of a way to automate the process
of
> "catching up" the application of the log files in the event of a failover?
> Specifically I am looking to automate the process of recognizing the last
> log applied and then applying the subsequent logs required to bring the DB
> back online.
>
> Any help would be appreciated.
>
> Thanks
>
> Ron
>
Monday, March 26, 2012
Log Shipping Restore Error 3456
Hi,
I've got a production server log shipping a 280GB database to a remote DR
site server. It has been running without incident for months now, but last
week it stopped restoring logs (the copy process was still running) with an
error 3456:
[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC SQL
Server Driver][SQL Server]Could not redo log record (500478:68225:4), for
transaction ID (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5).
Page: LSN = (500478:57038:10), type = 2. Log: OpCode = 2, context 3,
PrevPageLSN: (500478:68221:4).
I restored the remote database from a full tape backup shipped to the site
via courier (because we weren't sure if the link was to blame for the problem
restoring). Log shipping worked well again for a few days, but this morning
has again stopped restoring with the exact same error!
I've read the article "http://support.microsoft.com/kb/831950", and although
it describes the same error, it doesnt seem to apply to us as we weren't
doing any role changing, and weren't backing up the database manually with
the NORECOVERY switch (i.e. the backups were being done as usual by the log
shipping maint. plan).
Local server build is 8.00.997, and remote (DR) server build is 8.00.818.
Could the fact that they are slightly different versions have anything to do
with this problem?
Could this be corruption introducted by the network link? If so, is the
only way to fix this to fully restore the database again? Or is there some
way to get good copies of the log it failed on and restore those manually? I
don't have a huge amount of experience with log shipping, so any help would
be greatly appreciated - especially being a 24/7 mission critical DR server,
and in the middle of the holidays! Murphy's Law!
Thanks,
david
Hi David
The SQL Builds may cause a problem. I am not sure about it.
Rearding the file restore; it isn't necessary that you restore the entire
db. I would suggest the following steps:
1) Check the table msdb..log_shipping_plan_history for the last loaded file.
try
select * from msdb..log_shipping_plan_history order by endtime desc
2) Try restoring tha tfile manually from the Query Analyzer. try
restore log
<db_name>
from
disk = 'file_path'
with
standby = 'undo.txt'
3) if the above step succeeds keep restoring the successive trn files till
the step fails. Then that file at which the step fails is the corrupt file.
4) Copy only that file from the primary server and try step 2 with it.
5) re-run the LS Jobs and the ywill succeed.
Hope this helps.
I shall get back with mpre information about the affect of SQL builds on LS.
Thanks
Amer M J
MCP
"DavidCur" wrote:
> Hi,
> I've got a production server log shipping a 280GB database to a remote DR
> site server. It has been running without incident for months now, but last
> week it stopped restoring logs (the copy process was still running) with an
> error 3456:
> [Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Could not redo log record (500478:68225:4), for
> transaction ID (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5).
> Page: LSN = (500478:57038:10), type = 2. Log: OpCode = 2, context 3,
> PrevPageLSN: (500478:68221:4).
> I restored the remote database from a full tape backup shipped to the site
> via courier (because we weren't sure if the link was to blame for the problem
> restoring). Log shipping worked well again for a few days, but this morning
> has again stopped restoring with the exact same error!
> I've read the article "http://support.microsoft.com/kb/831950", and although
> it describes the same error, it doesnt seem to apply to us as we weren't
> doing any role changing, and weren't backing up the database manually with
> the NORECOVERY switch (i.e. the backups were being done as usual by the log
> shipping maint. plan).
> Local server build is 8.00.997, and remote (DR) server build is 8.00.818.
> Could the fact that they are slightly different versions have anything to do
> with this problem?
> Could this be corruption introducted by the network link? If so, is the
> only way to fix this to fully restore the database again? Or is there some
> way to get good copies of the log it failed on and restore those manually? I
> don't have a huge amount of experience with log shipping, so any help would
> be greatly appreciated - especially being a 24/7 mission critical DR server,
> and in the middle of the holidays! Murphy's Law!
>
> Thanks,
> david
|||Thanks for the quick response. The log files are copied and restored every
15 minutes. The problem occurred this morning around 12:15am. I've tried
your suggestion about restoring manually with the standby undo file.
I ran the following command (using the 12:00am file) successfully, but the
12:15am file produces the following output:
restore log JDE_Prod
from disk = 'K:\Backups\DRLogsIn\JDE_Prod_tlog_200512300015.TR N'
with standby = 'K:\Backups\DRLogsIn\LogUndo.tuf'
Deleting database file 'K:\Backups\DRLogsIn\LogUndo.tuf'.
Processed 34415 pages for database 'JDE_Prod', file 'JDE_PRODUCTION_log' on
file 1.
Server: Msg 3456, Level 21, State 1, Line 1
Could not redo log record (500478:68225:4), for transaction ID
(3:1085081790), on page (3:2789016), database 'JDE_Prod' (5). Page: LSN =
(500478:57038:10), type = 2. Log: OpCode = 2, context 3, PrevPageLSN:
(500478:68221:4).
Connection Broken
This 12:15 file has already been re-copied, but I will try again.
Would be interesting to see if you find any issues with different builds in
log shipping. The patches were applied to the local server a few months ago
(3 or 4 months), and log shipping has been running without incident this
whole time.
Thanks again,
Dave
"Amer M J" wrote:
> Hi David
> The SQL Builds may cause a problem. I am not sure about it.
> Rearding the file restore; it isn't necessary that you restore the entire
> db. I would suggest the following steps:
> 1) Check the table msdb..log_shipping_plan_history for the last loaded file.
> try
> select * from msdb..log_shipping_plan_history order by endtime desc
> 2) Try restoring tha tfile manually from the Query Analyzer. try
> restore log
> <db_name>
> from
> disk = 'file_path'
> with
> standby = 'undo.txt'
> 3) if the above step succeeds keep restoring the successive trn files till
> the step fails. Then that file at which the step fails is the corrupt file.
> 4) Copy only that file from the primary server and try step 2 with it.
> 5) re-run the LS Jobs and the ywill succeed.
> Hope this helps.
> I shall get back with mpre information about the affect of SQL builds on LS.
> Thanks
> Amer M J
> MCP
|||Hi Dave
I am curious here. Was the '.tuf' file deleted as per a part of the process
of manually ?
Also the builds do play a major role here. From what I can see as per your
information, the primary server is of a higher build than the secondary
server. So I was wondering how a log file of a db from a higher build was
getting restored onto a lower build server.
Also I would suggest checking out the integrity of the trn files on the
primary server. try
restore verifyonly command to check the backup set's integrity.
Please do check if anyother process is accessing the db on the secondary
server as this may disrupt the LS process.
Also check this link.
http://support.microsoft.com/kb/329487/en-us
Thanks
Amer M J
MCP
"DavidCur" wrote:
[vbcol=seagreen]
> Thanks for the quick response. The log files are copied and restored every
> 15 minutes. The problem occurred this morning around 12:15am. I've tried
> your suggestion about restoring manually with the standby undo file.
> I ran the following command (using the 12:00am file) successfully, but the
> 12:15am file produces the following output:
> restore log JDE_Prod
> from disk = 'K:\Backups\DRLogsIn\JDE_Prod_tlog_200512300015.TR N'
> with standby = 'K:\Backups\DRLogsIn\LogUndo.tuf'
> Deleting database file 'K:\Backups\DRLogsIn\LogUndo.tuf'.
> Processed 34415 pages for database 'JDE_Prod', file 'JDE_PRODUCTION_log' on
> file 1.
> Server: Msg 3456, Level 21, State 1, Line 1
> Could not redo log record (500478:68225:4), for transaction ID
> (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5). Page: LSN =
> (500478:57038:10), type = 2. Log: OpCode = 2, context 3, PrevPageLSN:
> (500478:68221:4).
> Connection Broken
> This 12:15 file has already been re-copied, but I will try again.
> Would be interesting to see if you find any issues with different builds in
> log shipping. The patches were applied to the local server a few months ago
> (3 or 4 months), and log shipping has been running without incident this
> whole time.
> Thanks again,
> Dave
>
> "Amer M J" wrote:
|||Hi again,
Yes, the standby file (whatever it has been called) is automatically deleted
by the restore process.
Good news though, I seem to have log shipping going again! :-)
I re-copied the 12:15am log file (yet again, 3rd time) and restored it with
the same syntax as in my previous post, and it worked. So the problem must
lie with our link to the remote DR server. Its now been logged to the
telecom company who provide the WAN pipe.
As a precautionary measure I will schedule the remote server to be patched
to the same build level as our local server (will be next year though as we
are in a "holiday change freeze" now).
Funnily enough, the restore headeronly, verifyonly and filelistonly all
seemed to work fine with the corrupt file. Is it possible that the header of
the file was okay, while the actual data was bad?
Thanks very much for the help though, and I will update with anything new we
find.
Dave
"Amer M J" wrote:
> Hi Dave
> I am curious here. Was the '.tuf' file deleted as per a part of the process
> of manually ?
> Also the builds do play a major role here. From what I can see as per your
> information, the primary server is of a higher build than the secondary
> server. So I was wondering how a log file of a db from a higher build was
> getting restored onto a lower build server.
> Also I would suggest checking out the integrity of the trn files on the
> primary server. try
> restore verifyonly command to check the backup set's integrity.
> Please do check if anyother process is accessing the db on the secondary
> server as this may disrupt the LS process.
> Also check this link.
> http://support.microsoft.com/kb/329487/en-us
> Thanks
> Amer M J
> MCP
sql
I've got a production server log shipping a 280GB database to a remote DR
site server. It has been running without incident for months now, but last
week it stopped restoring logs (the copy process was still running) with an
error 3456:
[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC SQL
Server Driver][SQL Server]Could not redo log record (500478:68225:4), for
transaction ID (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5).
Page: LSN = (500478:57038:10), type = 2. Log: OpCode = 2, context 3,
PrevPageLSN: (500478:68221:4).
I restored the remote database from a full tape backup shipped to the site
via courier (because we weren't sure if the link was to blame for the problem
restoring). Log shipping worked well again for a few days, but this morning
has again stopped restoring with the exact same error!
I've read the article "http://support.microsoft.com/kb/831950", and although
it describes the same error, it doesnt seem to apply to us as we weren't
doing any role changing, and weren't backing up the database manually with
the NORECOVERY switch (i.e. the backups were being done as usual by the log
shipping maint. plan).
Local server build is 8.00.997, and remote (DR) server build is 8.00.818.
Could the fact that they are slightly different versions have anything to do
with this problem?
Could this be corruption introducted by the network link? If so, is the
only way to fix this to fully restore the database again? Or is there some
way to get good copies of the log it failed on and restore those manually? I
don't have a huge amount of experience with log shipping, so any help would
be greatly appreciated - especially being a 24/7 mission critical DR server,
and in the middle of the holidays! Murphy's Law!
Thanks,
david
Hi David
The SQL Builds may cause a problem. I am not sure about it.
Rearding the file restore; it isn't necessary that you restore the entire
db. I would suggest the following steps:
1) Check the table msdb..log_shipping_plan_history for the last loaded file.
try
select * from msdb..log_shipping_plan_history order by endtime desc
2) Try restoring tha tfile manually from the Query Analyzer. try
restore log
<db_name>
from
disk = 'file_path'
with
standby = 'undo.txt'
3) if the above step succeeds keep restoring the successive trn files till
the step fails. Then that file at which the step fails is the corrupt file.
4) Copy only that file from the primary server and try step 2 with it.
5) re-run the LS Jobs and the ywill succeed.
Hope this helps.
I shall get back with mpre information about the affect of SQL builds on LS.
Thanks
Amer M J
MCP
"DavidCur" wrote:
> Hi,
> I've got a production server log shipping a 280GB database to a remote DR
> site server. It has been running without incident for months now, but last
> week it stopped restoring logs (the copy process was still running) with an
> error 3456:
> [Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Could not redo log record (500478:68225:4), for
> transaction ID (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5).
> Page: LSN = (500478:57038:10), type = 2. Log: OpCode = 2, context 3,
> PrevPageLSN: (500478:68221:4).
> I restored the remote database from a full tape backup shipped to the site
> via courier (because we weren't sure if the link was to blame for the problem
> restoring). Log shipping worked well again for a few days, but this morning
> has again stopped restoring with the exact same error!
> I've read the article "http://support.microsoft.com/kb/831950", and although
> it describes the same error, it doesnt seem to apply to us as we weren't
> doing any role changing, and weren't backing up the database manually with
> the NORECOVERY switch (i.e. the backups were being done as usual by the log
> shipping maint. plan).
> Local server build is 8.00.997, and remote (DR) server build is 8.00.818.
> Could the fact that they are slightly different versions have anything to do
> with this problem?
> Could this be corruption introducted by the network link? If so, is the
> only way to fix this to fully restore the database again? Or is there some
> way to get good copies of the log it failed on and restore those manually? I
> don't have a huge amount of experience with log shipping, so any help would
> be greatly appreciated - especially being a 24/7 mission critical DR server,
> and in the middle of the holidays! Murphy's Law!
>
> Thanks,
> david
|||Thanks for the quick response. The log files are copied and restored every
15 minutes. The problem occurred this morning around 12:15am. I've tried
your suggestion about restoring manually with the standby undo file.
I ran the following command (using the 12:00am file) successfully, but the
12:15am file produces the following output:
restore log JDE_Prod
from disk = 'K:\Backups\DRLogsIn\JDE_Prod_tlog_200512300015.TR N'
with standby = 'K:\Backups\DRLogsIn\LogUndo.tuf'
Deleting database file 'K:\Backups\DRLogsIn\LogUndo.tuf'.
Processed 34415 pages for database 'JDE_Prod', file 'JDE_PRODUCTION_log' on
file 1.
Server: Msg 3456, Level 21, State 1, Line 1
Could not redo log record (500478:68225:4), for transaction ID
(3:1085081790), on page (3:2789016), database 'JDE_Prod' (5). Page: LSN =
(500478:57038:10), type = 2. Log: OpCode = 2, context 3, PrevPageLSN:
(500478:68221:4).
Connection Broken
This 12:15 file has already been re-copied, but I will try again.
Would be interesting to see if you find any issues with different builds in
log shipping. The patches were applied to the local server a few months ago
(3 or 4 months), and log shipping has been running without incident this
whole time.
Thanks again,
Dave
"Amer M J" wrote:
> Hi David
> The SQL Builds may cause a problem. I am not sure about it.
> Rearding the file restore; it isn't necessary that you restore the entire
> db. I would suggest the following steps:
> 1) Check the table msdb..log_shipping_plan_history for the last loaded file.
> try
> select * from msdb..log_shipping_plan_history order by endtime desc
> 2) Try restoring tha tfile manually from the Query Analyzer. try
> restore log
> <db_name>
> from
> disk = 'file_path'
> with
> standby = 'undo.txt'
> 3) if the above step succeeds keep restoring the successive trn files till
> the step fails. Then that file at which the step fails is the corrupt file.
> 4) Copy only that file from the primary server and try step 2 with it.
> 5) re-run the LS Jobs and the ywill succeed.
> Hope this helps.
> I shall get back with mpre information about the affect of SQL builds on LS.
> Thanks
> Amer M J
> MCP
|||Hi Dave
I am curious here. Was the '.tuf' file deleted as per a part of the process
of manually ?
Also the builds do play a major role here. From what I can see as per your
information, the primary server is of a higher build than the secondary
server. So I was wondering how a log file of a db from a higher build was
getting restored onto a lower build server.
Also I would suggest checking out the integrity of the trn files on the
primary server. try
restore verifyonly command to check the backup set's integrity.
Please do check if anyother process is accessing the db on the secondary
server as this may disrupt the LS process.
Also check this link.
http://support.microsoft.com/kb/329487/en-us
Thanks
Amer M J
MCP
"DavidCur" wrote:
[vbcol=seagreen]
> Thanks for the quick response. The log files are copied and restored every
> 15 minutes. The problem occurred this morning around 12:15am. I've tried
> your suggestion about restoring manually with the standby undo file.
> I ran the following command (using the 12:00am file) successfully, but the
> 12:15am file produces the following output:
> restore log JDE_Prod
> from disk = 'K:\Backups\DRLogsIn\JDE_Prod_tlog_200512300015.TR N'
> with standby = 'K:\Backups\DRLogsIn\LogUndo.tuf'
> Deleting database file 'K:\Backups\DRLogsIn\LogUndo.tuf'.
> Processed 34415 pages for database 'JDE_Prod', file 'JDE_PRODUCTION_log' on
> file 1.
> Server: Msg 3456, Level 21, State 1, Line 1
> Could not redo log record (500478:68225:4), for transaction ID
> (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5). Page: LSN =
> (500478:57038:10), type = 2. Log: OpCode = 2, context 3, PrevPageLSN:
> (500478:68221:4).
> Connection Broken
> This 12:15 file has already been re-copied, but I will try again.
> Would be interesting to see if you find any issues with different builds in
> log shipping. The patches were applied to the local server a few months ago
> (3 or 4 months), and log shipping has been running without incident this
> whole time.
> Thanks again,
> Dave
>
> "Amer M J" wrote:
|||Hi again,
Yes, the standby file (whatever it has been called) is automatically deleted
by the restore process.
Good news though, I seem to have log shipping going again! :-)
I re-copied the 12:15am log file (yet again, 3rd time) and restored it with
the same syntax as in my previous post, and it worked. So the problem must
lie with our link to the remote DR server. Its now been logged to the
telecom company who provide the WAN pipe.
As a precautionary measure I will schedule the remote server to be patched
to the same build level as our local server (will be next year though as we
are in a "holiday change freeze" now).
Funnily enough, the restore headeronly, verifyonly and filelistonly all
seemed to work fine with the corrupt file. Is it possible that the header of
the file was okay, while the actual data was bad?
Thanks very much for the help though, and I will update with anything new we
find.
Dave
"Amer M J" wrote:
> Hi Dave
> I am curious here. Was the '.tuf' file deleted as per a part of the process
> of manually ?
> Also the builds do play a major role here. From what I can see as per your
> information, the primary server is of a higher build than the secondary
> server. So I was wondering how a log file of a db from a higher build was
> getting restored onto a lower build server.
> Also I would suggest checking out the integrity of the trn files on the
> primary server. try
> restore verifyonly command to check the backup set's integrity.
> Please do check if anyother process is accessing the db on the secondary
> server as this may disrupt the LS process.
> Also check this link.
> http://support.microsoft.com/kb/329487/en-us
> Thanks
> Amer M J
> MCP
sql
Log Shipping Restore Error 3456
Hi,
I've got a production server log shipping a 280GB database to a remote DR
site server. It has been running without incident for months now, but last
week it stopped restoring logs (the copy process was still running) with an
error 3456:
[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Could not redo log record (500478:68225:4), fo
r
transaction ID (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5).
Page: LSN = (500478:57038:10), type = 2. Log: OpCode = 2, context 3,
PrevPageLSN: (500478:68221:4).
I restored the remote database from a full tape backup shipped to the site
via courier (because we weren't sure if the link was to blame for the proble
m
restoring). Log shipping worked well again for a few days, but this morning
has again stopped restoring with the exact same error!
I've read the article "http://support.microsoft.com/kb/831950", and although
it describes the same error, it doesnt seem to apply to us as we weren't
doing any role changing, and weren't backing up the database manually with
the NORECOVERY switch (i.e. the backups were being done as usual by the log
shipping maint. plan).
Local server build is 8.00.997, and remote (DR) server build is 8.00.818.
Could the fact that they are slightly different versions have anything to do
with this problem?
Could this be corruption introducted by the network link? If so, is the
only way to fix this to fully restore the database again? Or is there some
way to get good copies of the log it failed on and restore those manually?
I
don't have a huge amount of experience with log shipping, so any help would
be greatly appreciated - especially being a 24/7 mission critical DR server,
and in the middle of the holidays! Murphy's Law!
Thanks,
davidHi David
The SQL Builds may cause a problem. I am not sure about it.
Rearding the file restore; it isn't necessary that you restore the entire
db. I would suggest the following steps:
1) Check the table msdb..log_shipping_plan_history for the last loaded file.
try
select * from msdb..log_shipping_plan_history order by endtime desc
2) Try restoring tha tfile manually from the Query Analyzer. try
restore log
<db_name>
from
disk = 'file_path'
with
standby = 'undo.txt'
3) if the above step succeeds keep restoring the successive trn files till
the step fails. Then that file at which the step fails is the corrupt file.
4) Copy only that file from the primary server and try step 2 with it.
5) re-run the LS Jobs and the ywill succeed.
Hope this helps.
I shall get back with mpre information about the affect of SQL builds on LS.
Thanks
Amer M J
MCP
"DavidCur" wrote:
> Hi,
> I've got a production server log shipping a 280GB database to a remote DR
> site server. It has been running without incident for months now, but las
t
> week it stopped restoring logs (the copy process was still running) with a
n
> error 3456:
> [Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]Could not redo log record (500478:68225:4),
for
> transaction ID (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5
).
> Page: LSN = (500478:57038:10), type = 2. Log: OpCode = 2, context 3,
> PrevPageLSN: (500478:68221:4).
> I restored the remote database from a full tape backup shipped to the site
> via courier (because we weren't sure if the link was to blame for the prob
lem
> restoring). Log shipping worked well again for a few days, but this morni
ng
> has again stopped restoring with the exact same error!
> I've read the article "http://support.microsoft.com/kb/831950", and althou
gh
> it describes the same error, it doesnt seem to apply to us as we weren't
> doing any role changing, and weren't backing up the database manually with
> the NORECOVERY switch (i.e. the backups were being done as usual by the lo
g
> shipping maint. plan).
> Local server build is 8.00.997, and remote (DR) server build is 8.00.818.
> Could the fact that they are slightly different versions have anything to
do
> with this problem?
> Could this be corruption introducted by the network link? If so, is the
> only way to fix this to fully restore the database again? Or is there some
> way to get good copies of the log it failed on and restore those manually?
I
> don't have a huge amount of experience with log shipping, so any help woul
d
> be greatly appreciated - especially being a 24/7 mission critical DR serve
r,
> and in the middle of the holidays! Murphy's Law!
>
> Thanks,
> david|||Thanks for the quick response. The log files are copied and restored every
15 minutes. The problem occurred this morning around 12:15am. I've tried
your suggestion about restoring manually with the standby undo file.
I ran the following command (using the 12:00am file) successfully, but the
12:15am file produces the following output:
---
restore log JDE_Prod
from disk = 'K:\Backups\DRLogsIn\JDE_Prod_tlog_20051
2300015.TRN'
with standby = 'K:\Backups\DRLogsIn\LogUndo.tuf'
---
Deleting database file 'K:\Backups\DRLogsIn\LogUndo.tuf'.
Processed 34415 pages for database 'JDE_Prod', file 'JDE_PRODUCTION_log' on
file 1.
Server: Msg 3456, Level 21, State 1, Line 1
Could not redo log record (500478:68225:4), for transaction ID
(3:1085081790), on page (3:2789016), database 'JDE_Prod' (5). Page: LSN =
(500478:57038:10), type = 2. Log: OpCode = 2, context 3, PrevPageLSN:
(500478:68221:4).
Connection Broken
---
This 12:15 file has already been re-copied, but I will try again.
Would be interesting to see if you find any issues with different builds in
log shipping. The patches were applied to the local server a few months ago
(3 or 4 months), and log shipping has been running without incident this
whole time.
Thanks again,
Dave
"Amer M J" wrote:
> Hi David
> The SQL Builds may cause a problem. I am not sure about it.
> Rearding the file restore; it isn't necessary that you restore the entire
> db. I would suggest the following steps:
> 1) Check the table msdb..log_shipping_plan_history for the last loaded fil
e.
> try
> select * from msdb..log_shipping_plan_history order by endtime desc
> 2) Try restoring tha tfile manually from the Query Analyzer. try
> restore log
> <db_name>
> from
> disk = 'file_path'
> with
> standby = 'undo.txt'
> 3) if the above step succeeds keep restoring the successive trn files till
> the step fails. Then that file at which the step fails is the corrupt file
.
> 4) Copy only that file from the primary server and try step 2 with it.
> 5) re-run the LS Jobs and the ywill succeed.
> Hope this helps.
> I shall get back with mpre information about the affect of SQL builds on L
S.
> Thanks
> Amer M J
> MCP|||Hi Dave
I am curious here. Was the '.tuf' file deleted as per a part of the process
of manually ?
Also the builds do play a major role here. From what I can see as per your
information, the primary server is of a higher build than the secondary
server. So I was wondering how a log file of a db from a higher build was
getting restored onto a lower build server.
Also I would suggest checking out the integrity of the trn files on the
primary server. try
restore verifyonly command to check the backup set's integrity.
Please do check if anyother process is accessing the db on the secondary
server as this may disrupt the LS process.
Also check this link.
http://support.microsoft.com/kb/329487/en-us
Thanks
Amer M J
MCP
"DavidCur" wrote:
[vbcol=seagreen]
> Thanks for the quick response. The log files are copied and restored ever
y
> 15 minutes. The problem occurred this morning around 12:15am. I've tried
> your suggestion about restoring manually with the standby undo file.
> I ran the following command (using the 12:00am file) successfully, but the
> 12:15am file produces the following output:
> ---
> restore log JDE_Prod
> from disk = 'K:\Backups\DRLogsIn\JDE_Prod_tlog_20051
2300015.TRN'
> with standby = 'K:\Backups\DRLogsIn\LogUndo.tuf'
> ---
> Deleting database file 'K:\Backups\DRLogsIn\LogUndo.tuf'.
> Processed 34415 pages for database 'JDE_Prod', file 'JDE_PRODUCTION_log' o
n
> file 1.
> Server: Msg 3456, Level 21, State 1, Line 1
> Could not redo log record (500478:68225:4), for transaction ID
> (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5). Page: LSN =
> (500478:57038:10), type = 2. Log: OpCode = 2, context 3, PrevPageLSN:
> (500478:68221:4).
> Connection Broken
> ---
> This 12:15 file has already been re-copied, but I will try again.
> Would be interesting to see if you find any issues with different builds i
n
> log shipping. The patches were applied to the local server a few months a
go
> (3 or 4 months), and log shipping has been running without incident this
> whole time.
> Thanks again,
> Dave
>
> "Amer M J" wrote:
>|||Hi again,
Yes, the standby file (whatever it has been called) is automatically deleted
by the restore process.
Good news though, I seem to have log shipping going again! :-)
I re-copied the 12:15am log file (yet again, 3rd time) and restored it with
the same syntax as in my previous post, and it worked. So the problem must
lie with our link to the remote DR server. Its now been logged to the
telecom company who provide the WAN pipe.
As a precautionary measure I will schedule the remote server to be patched
to the same build level as our local server (will be next year though as we
are in a "holiday change freeze" now).
Funnily enough, the restore headeronly, verifyonly and filelistonly all
seemed to work fine with the corrupt file. Is it possible that the header o
f
the file was okay, while the actual data was bad'
Thanks very much for the help though, and I will update with anything new we
find.
Dave
"Amer M J" wrote:
> Hi Dave
> I am curious here. Was the '.tuf' file deleted as per a part of the proces
s
> of manually ?
> Also the builds do play a major role here. From what I can see as per your
> information, the primary server is of a higher build than the secondary
> server. So I was wondering how a log file of a db from a higher build was
> getting restored onto a lower build server.
> Also I would suggest checking out the integrity of the trn files on the
> primary server. try
> restore verifyonly command to check the backup set's integrity.
> Please do check if anyother process is accessing the db on the secondary
> server as this may disrupt the LS process.
> Also check this link.
> http://support.microsoft.com/kb/329487/en-us
> Thanks
> Amer M J
> MCP
I've got a production server log shipping a 280GB database to a remote DR
site server. It has been running without incident for months now, but last
week it stopped restoring logs (the copy process was still running) with an
error 3456:
[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Could not redo log record (500478:68225:4), fo
r
transaction ID (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5).
Page: LSN = (500478:57038:10), type = 2. Log: OpCode = 2, context 3,
PrevPageLSN: (500478:68221:4).
I restored the remote database from a full tape backup shipped to the site
via courier (because we weren't sure if the link was to blame for the proble
m
restoring). Log shipping worked well again for a few days, but this morning
has again stopped restoring with the exact same error!
I've read the article "http://support.microsoft.com/kb/831950", and although
it describes the same error, it doesnt seem to apply to us as we weren't
doing any role changing, and weren't backing up the database manually with
the NORECOVERY switch (i.e. the backups were being done as usual by the log
shipping maint. plan).
Local server build is 8.00.997, and remote (DR) server build is 8.00.818.
Could the fact that they are slightly different versions have anything to do
with this problem?
Could this be corruption introducted by the network link? If so, is the
only way to fix this to fully restore the database again? Or is there some
way to get good copies of the log it failed on and restore those manually?
I
don't have a huge amount of experience with log shipping, so any help would
be greatly appreciated - especially being a 24/7 mission critical DR server,
and in the middle of the holidays! Murphy's Law!
Thanks,
davidHi David
The SQL Builds may cause a problem. I am not sure about it.
Rearding the file restore; it isn't necessary that you restore the entire
db. I would suggest the following steps:
1) Check the table msdb..log_shipping_plan_history for the last loaded file.
try
select * from msdb..log_shipping_plan_history order by endtime desc
2) Try restoring tha tfile manually from the Query Analyzer. try
restore log
<db_name>
from
disk = 'file_path'
with
standby = 'undo.txt'
3) if the above step succeeds keep restoring the successive trn files till
the step fails. Then that file at which the step fails is the corrupt file.
4) Copy only that file from the primary server and try step 2 with it.
5) re-run the LS Jobs and the ywill succeed.
Hope this helps.
I shall get back with mpre information about the affect of SQL builds on LS.
Thanks
Amer M J
MCP
"DavidCur" wrote:
> Hi,
> I've got a production server log shipping a 280GB database to a remote DR
> site server. It has been running without incident for months now, but las
t
> week it stopped restoring logs (the copy process was still running) with a
n
> error 3456:
> [Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]Could not redo log record (500478:68225:4),
for
> transaction ID (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5
).
> Page: LSN = (500478:57038:10), type = 2. Log: OpCode = 2, context 3,
> PrevPageLSN: (500478:68221:4).
> I restored the remote database from a full tape backup shipped to the site
> via courier (because we weren't sure if the link was to blame for the prob
lem
> restoring). Log shipping worked well again for a few days, but this morni
ng
> has again stopped restoring with the exact same error!
> I've read the article "http://support.microsoft.com/kb/831950", and althou
gh
> it describes the same error, it doesnt seem to apply to us as we weren't
> doing any role changing, and weren't backing up the database manually with
> the NORECOVERY switch (i.e. the backups were being done as usual by the lo
g
> shipping maint. plan).
> Local server build is 8.00.997, and remote (DR) server build is 8.00.818.
> Could the fact that they are slightly different versions have anything to
do
> with this problem?
> Could this be corruption introducted by the network link? If so, is the
> only way to fix this to fully restore the database again? Or is there some
> way to get good copies of the log it failed on and restore those manually?
I
> don't have a huge amount of experience with log shipping, so any help woul
d
> be greatly appreciated - especially being a 24/7 mission critical DR serve
r,
> and in the middle of the holidays! Murphy's Law!
>
> Thanks,
> david|||Thanks for the quick response. The log files are copied and restored every
15 minutes. The problem occurred this morning around 12:15am. I've tried
your suggestion about restoring manually with the standby undo file.
I ran the following command (using the 12:00am file) successfully, but the
12:15am file produces the following output:
---
restore log JDE_Prod
from disk = 'K:\Backups\DRLogsIn\JDE_Prod_tlog_20051
2300015.TRN'
with standby = 'K:\Backups\DRLogsIn\LogUndo.tuf'
---
Deleting database file 'K:\Backups\DRLogsIn\LogUndo.tuf'.
Processed 34415 pages for database 'JDE_Prod', file 'JDE_PRODUCTION_log' on
file 1.
Server: Msg 3456, Level 21, State 1, Line 1
Could not redo log record (500478:68225:4), for transaction ID
(3:1085081790), on page (3:2789016), database 'JDE_Prod' (5). Page: LSN =
(500478:57038:10), type = 2. Log: OpCode = 2, context 3, PrevPageLSN:
(500478:68221:4).
Connection Broken
---
This 12:15 file has already been re-copied, but I will try again.
Would be interesting to see if you find any issues with different builds in
log shipping. The patches were applied to the local server a few months ago
(3 or 4 months), and log shipping has been running without incident this
whole time.
Thanks again,
Dave
"Amer M J" wrote:
> Hi David
> The SQL Builds may cause a problem. I am not sure about it.
> Rearding the file restore; it isn't necessary that you restore the entire
> db. I would suggest the following steps:
> 1) Check the table msdb..log_shipping_plan_history for the last loaded fil
e.
> try
> select * from msdb..log_shipping_plan_history order by endtime desc
> 2) Try restoring tha tfile manually from the Query Analyzer. try
> restore log
> <db_name>
> from
> disk = 'file_path'
> with
> standby = 'undo.txt'
> 3) if the above step succeeds keep restoring the successive trn files till
> the step fails. Then that file at which the step fails is the corrupt file
.
> 4) Copy only that file from the primary server and try step 2 with it.
> 5) re-run the LS Jobs and the ywill succeed.
> Hope this helps.
> I shall get back with mpre information about the affect of SQL builds on L
S.
> Thanks
> Amer M J
> MCP|||Hi Dave
I am curious here. Was the '.tuf' file deleted as per a part of the process
of manually ?
Also the builds do play a major role here. From what I can see as per your
information, the primary server is of a higher build than the secondary
server. So I was wondering how a log file of a db from a higher build was
getting restored onto a lower build server.
Also I would suggest checking out the integrity of the trn files on the
primary server. try
restore verifyonly command to check the backup set's integrity.
Please do check if anyother process is accessing the db on the secondary
server as this may disrupt the LS process.
Also check this link.
http://support.microsoft.com/kb/329487/en-us
Thanks
Amer M J
MCP
"DavidCur" wrote:
[vbcol=seagreen]
> Thanks for the quick response. The log files are copied and restored ever
y
> 15 minutes. The problem occurred this morning around 12:15am. I've tried
> your suggestion about restoring manually with the standby undo file.
> I ran the following command (using the 12:00am file) successfully, but the
> 12:15am file produces the following output:
> ---
> restore log JDE_Prod
> from disk = 'K:\Backups\DRLogsIn\JDE_Prod_tlog_20051
2300015.TRN'
> with standby = 'K:\Backups\DRLogsIn\LogUndo.tuf'
> ---
> Deleting database file 'K:\Backups\DRLogsIn\LogUndo.tuf'.
> Processed 34415 pages for database 'JDE_Prod', file 'JDE_PRODUCTION_log' o
n
> file 1.
> Server: Msg 3456, Level 21, State 1, Line 1
> Could not redo log record (500478:68225:4), for transaction ID
> (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5). Page: LSN =
> (500478:57038:10), type = 2. Log: OpCode = 2, context 3, PrevPageLSN:
> (500478:68221:4).
> Connection Broken
> ---
> This 12:15 file has already been re-copied, but I will try again.
> Would be interesting to see if you find any issues with different builds i
n
> log shipping. The patches were applied to the local server a few months a
go
> (3 or 4 months), and log shipping has been running without incident this
> whole time.
> Thanks again,
> Dave
>
> "Amer M J" wrote:
>|||Hi again,
Yes, the standby file (whatever it has been called) is automatically deleted
by the restore process.
Good news though, I seem to have log shipping going again! :-)
I re-copied the 12:15am log file (yet again, 3rd time) and restored it with
the same syntax as in my previous post, and it worked. So the problem must
lie with our link to the remote DR server. Its now been logged to the
telecom company who provide the WAN pipe.
As a precautionary measure I will schedule the remote server to be patched
to the same build level as our local server (will be next year though as we
are in a "holiday change freeze" now).
Funnily enough, the restore headeronly, verifyonly and filelistonly all
seemed to work fine with the corrupt file. Is it possible that the header o
f
the file was okay, while the actual data was bad'
Thanks very much for the help though, and I will update with anything new we
find.
Dave
"Amer M J" wrote:
> Hi Dave
> I am curious here. Was the '.tuf' file deleted as per a part of the proces
s
> of manually ?
> Also the builds do play a major role here. From what I can see as per your
> information, the primary server is of a higher build than the secondary
> server. So I was wondering how a log file of a db from a higher build was
> getting restored onto a lower build server.
> Also I would suggest checking out the integrity of the trn files on the
> primary server. try
> restore verifyonly command to check the backup set's integrity.
> Please do check if anyother process is accessing the db on the secondary
> server as this may disrupt the LS process.
> Also check this link.
> http://support.microsoft.com/kb/329487/en-us
> Thanks
> Amer M J
> MCP
Log Shipping Restore Error 3456
Hi,
I've got a production server log shipping a 280GB database to a remote DR
site server. It has been running without incident for months now, but last
week it stopped restoring logs (the copy process was still running) with an
error 3456:
[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC SQL
Server Driver][SQL Server]Could not redo log record (500478:68225:4), for
transaction ID (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5).
Page: LSN = (500478:57038:10), type = 2. Log: OpCode = 2, context 3,
PrevPageLSN: (500478:68221:4).
I restored the remote database from a full tape backup shipped to the site
via courier (because we weren't sure if the link was to blame for the problem
restoring). Log shipping worked well again for a few days, but this morning
has again stopped restoring with the exact same error!
I've read the article "http://support.microsoft.com/kb/831950", and although
it describes the same error, it doesnt seem to apply to us as we weren't
doing any role changing, and weren't backing up the database manually with
the NORECOVERY switch (i.e. the backups were being done as usual by the log
shipping maint. plan).
Local server build is 8.00.997, and remote (DR) server build is 8.00.818.
Could the fact that they are slightly different versions have anything to do
with this problem?
Could this be corruption introducted by the network link? If so, is the
only way to fix this to fully restore the database again? Or is there some
way to get good copies of the log it failed on and restore those manually? I
don't have a huge amount of experience with log shipping, so any help would
be greatly appreciated - especially being a 24/7 mission critical DR server,
and in the middle of the holidays! Murphy's Law!
Thanks,
davidHi David
The SQL Builds may cause a problem. I am not sure about it.
Rearding the file restore; it isn't necessary that you restore the entire
db. I would suggest the following steps:
1) Check the table msdb..log_shipping_plan_history for the last loaded file.
try
select * from msdb..log_shipping_plan_history order by endtime desc
2) Try restoring tha tfile manually from the Query Analyzer. try
restore log
<db_name>
from
disk = 'file_path'
with
standby = 'undo.txt'
3) if the above step succeeds keep restoring the successive trn files till
the step fails. Then that file at which the step fails is the corrupt file.
4) Copy only that file from the primary server and try step 2 with it.
5) re-run the LS Jobs and the ywill succeed.
Hope this helps.
I shall get back with mpre information about the affect of SQL builds on LS.
Thanks
Amer M J
MCP
"DavidCur" wrote:
> Hi,
> I've got a production server log shipping a 280GB database to a remote DR
> site server. It has been running without incident for months now, but last
> week it stopped restoring logs (the copy process was still running) with an
> error 3456:
> [Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Could not redo log record (500478:68225:4), for
> transaction ID (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5).
> Page: LSN = (500478:57038:10), type = 2. Log: OpCode = 2, context 3,
> PrevPageLSN: (500478:68221:4).
> I restored the remote database from a full tape backup shipped to the site
> via courier (because we weren't sure if the link was to blame for the problem
> restoring). Log shipping worked well again for a few days, but this morning
> has again stopped restoring with the exact same error!
> I've read the article "http://support.microsoft.com/kb/831950", and although
> it describes the same error, it doesnt seem to apply to us as we weren't
> doing any role changing, and weren't backing up the database manually with
> the NORECOVERY switch (i.e. the backups were being done as usual by the log
> shipping maint. plan).
> Local server build is 8.00.997, and remote (DR) server build is 8.00.818.
> Could the fact that they are slightly different versions have anything to do
> with this problem?
> Could this be corruption introducted by the network link? If so, is the
> only way to fix this to fully restore the database again? Or is there some
> way to get good copies of the log it failed on and restore those manually? I
> don't have a huge amount of experience with log shipping, so any help would
> be greatly appreciated - especially being a 24/7 mission critical DR server,
> and in the middle of the holidays! Murphy's Law!
>
> Thanks,
> david|||Thanks for the quick response. The log files are copied and restored every
15 minutes. The problem occurred this morning around 12:15am. I've tried
your suggestion about restoring manually with the standby undo file.
I ran the following command (using the 12:00am file) successfully, but the
12:15am file produces the following output:
---
restore log JDE_Prod
from disk = 'K:\Backups\DRLogsIn\JDE_Prod_tlog_200512300015.TRN'
with standby = 'K:\Backups\DRLogsIn\LogUndo.tuf'
---
Deleting database file 'K:\Backups\DRLogsIn\LogUndo.tuf'.
Processed 34415 pages for database 'JDE_Prod', file 'JDE_PRODUCTION_log' on
file 1.
Server: Msg 3456, Level 21, State 1, Line 1
Could not redo log record (500478:68225:4), for transaction ID
(3:1085081790), on page (3:2789016), database 'JDE_Prod' (5). Page: LSN =(500478:57038:10), type = 2. Log: OpCode = 2, context 3, PrevPageLSN:
(500478:68221:4).
Connection Broken
---
This 12:15 file has already been re-copied, but I will try again.
Would be interesting to see if you find any issues with different builds in
log shipping. The patches were applied to the local server a few months ago
(3 or 4 months), and log shipping has been running without incident this
whole time.
Thanks again,
Dave
"Amer M J" wrote:
> Hi David
> The SQL Builds may cause a problem. I am not sure about it.
> Rearding the file restore; it isn't necessary that you restore the entire
> db. I would suggest the following steps:
> 1) Check the table msdb..log_shipping_plan_history for the last loaded file.
> try
> select * from msdb..log_shipping_plan_history order by endtime desc
> 2) Try restoring tha tfile manually from the Query Analyzer. try
> restore log
> <db_name>
> from
> disk = 'file_path'
> with
> standby = 'undo.txt'
> 3) if the above step succeeds keep restoring the successive trn files till
> the step fails. Then that file at which the step fails is the corrupt file.
> 4) Copy only that file from the primary server and try step 2 with it.
> 5) re-run the LS Jobs and the ywill succeed.
> Hope this helps.
> I shall get back with mpre information about the affect of SQL builds on LS.
> Thanks
> Amer M J
> MCP|||Hi Dave
I am curious here. Was the '.tuf' file deleted as per a part of the process
of manually ?
Also the builds do play a major role here. From what I can see as per your
information, the primary server is of a higher build than the secondary
server. So I was wondering how a log file of a db from a higher build was
getting restored onto a lower build server.
Also I would suggest checking out the integrity of the trn files on the
primary server. try
restore verifyonly command to check the backup set's integrity.
Please do check if anyother process is accessing the db on the secondary
server as this may disrupt the LS process.
Also check this link.
http://support.microsoft.com/kb/329487/en-us
Thanks
Amer M J
MCP
"DavidCur" wrote:
> Thanks for the quick response. The log files are copied and restored every
> 15 minutes. The problem occurred this morning around 12:15am. I've tried
> your suggestion about restoring manually with the standby undo file.
> I ran the following command (using the 12:00am file) successfully, but the
> 12:15am file produces the following output:
> ---
> restore log JDE_Prod
> from disk = 'K:\Backups\DRLogsIn\JDE_Prod_tlog_200512300015.TRN'
> with standby = 'K:\Backups\DRLogsIn\LogUndo.tuf'
> ---
> Deleting database file 'K:\Backups\DRLogsIn\LogUndo.tuf'.
> Processed 34415 pages for database 'JDE_Prod', file 'JDE_PRODUCTION_log' on
> file 1.
> Server: Msg 3456, Level 21, State 1, Line 1
> Could not redo log record (500478:68225:4), for transaction ID
> (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5). Page: LSN => (500478:57038:10), type = 2. Log: OpCode = 2, context 3, PrevPageLSN:
> (500478:68221:4).
> Connection Broken
> ---
> This 12:15 file has already been re-copied, but I will try again.
> Would be interesting to see if you find any issues with different builds in
> log shipping. The patches were applied to the local server a few months ago
> (3 or 4 months), and log shipping has been running without incident this
> whole time.
> Thanks again,
> Dave
>
> "Amer M J" wrote:
> > Hi David
> >
> > The SQL Builds may cause a problem. I am not sure about it.
> >
> > Rearding the file restore; it isn't necessary that you restore the entire
> > db. I would suggest the following steps:
> >
> > 1) Check the table msdb..log_shipping_plan_history for the last loaded file.
> > try
> >
> > select * from msdb..log_shipping_plan_history order by endtime desc
> >
> > 2) Try restoring tha tfile manually from the Query Analyzer. try
> >
> > restore log
> > <db_name>
> > from
> > disk = 'file_path'
> > with
> > standby = 'undo.txt'
> >
> > 3) if the above step succeeds keep restoring the successive trn files till
> > the step fails. Then that file at which the step fails is the corrupt file.
> >
> > 4) Copy only that file from the primary server and try step 2 with it.
> >
> > 5) re-run the LS Jobs and the ywill succeed.
> >
> > Hope this helps.
> >
> > I shall get back with mpre information about the affect of SQL builds on LS.
> >
> > Thanks
> > Amer M J
> > MCP|||Hi again,
Yes, the standby file (whatever it has been called) is automatically deleted
by the restore process.
Good news though, I seem to have log shipping going again! :-)
I re-copied the 12:15am log file (yet again, 3rd time) and restored it with
the same syntax as in my previous post, and it worked. So the problem must
lie with our link to the remote DR server. Its now been logged to the
telecom company who provide the WAN pipe.
As a precautionary measure I will schedule the remote server to be patched
to the same build level as our local server (will be next year though as we
are in a "holiday change freeze" now).
Funnily enough, the restore headeronly, verifyonly and filelistonly all
seemed to work fine with the corrupt file. Is it possible that the header of
the file was okay, while the actual data was bad'
Thanks very much for the help though, and I will update with anything new we
find.
Dave
"Amer M J" wrote:
> Hi Dave
> I am curious here. Was the '.tuf' file deleted as per a part of the process
> of manually ?
> Also the builds do play a major role here. From what I can see as per your
> information, the primary server is of a higher build than the secondary
> server. So I was wondering how a log file of a db from a higher build was
> getting restored onto a lower build server.
> Also I would suggest checking out the integrity of the trn files on the
> primary server. try
> restore verifyonly command to check the backup set's integrity.
> Please do check if anyother process is accessing the db on the secondary
> server as this may disrupt the LS process.
> Also check this link.
> http://support.microsoft.com/kb/329487/en-us
> Thanks
> Amer M J
> MCP
I've got a production server log shipping a 280GB database to a remote DR
site server. It has been running without incident for months now, but last
week it stopped restoring logs (the copy process was still running) with an
error 3456:
[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC SQL
Server Driver][SQL Server]Could not redo log record (500478:68225:4), for
transaction ID (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5).
Page: LSN = (500478:57038:10), type = 2. Log: OpCode = 2, context 3,
PrevPageLSN: (500478:68221:4).
I restored the remote database from a full tape backup shipped to the site
via courier (because we weren't sure if the link was to blame for the problem
restoring). Log shipping worked well again for a few days, but this morning
has again stopped restoring with the exact same error!
I've read the article "http://support.microsoft.com/kb/831950", and although
it describes the same error, it doesnt seem to apply to us as we weren't
doing any role changing, and weren't backing up the database manually with
the NORECOVERY switch (i.e. the backups were being done as usual by the log
shipping maint. plan).
Local server build is 8.00.997, and remote (DR) server build is 8.00.818.
Could the fact that they are slightly different versions have anything to do
with this problem?
Could this be corruption introducted by the network link? If so, is the
only way to fix this to fully restore the database again? Or is there some
way to get good copies of the log it failed on and restore those manually? I
don't have a huge amount of experience with log shipping, so any help would
be greatly appreciated - especially being a 24/7 mission critical DR server,
and in the middle of the holidays! Murphy's Law!
Thanks,
davidHi David
The SQL Builds may cause a problem. I am not sure about it.
Rearding the file restore; it isn't necessary that you restore the entire
db. I would suggest the following steps:
1) Check the table msdb..log_shipping_plan_history for the last loaded file.
try
select * from msdb..log_shipping_plan_history order by endtime desc
2) Try restoring tha tfile manually from the Query Analyzer. try
restore log
<db_name>
from
disk = 'file_path'
with
standby = 'undo.txt'
3) if the above step succeeds keep restoring the successive trn files till
the step fails. Then that file at which the step fails is the corrupt file.
4) Copy only that file from the primary server and try step 2 with it.
5) re-run the LS Jobs and the ywill succeed.
Hope this helps.
I shall get back with mpre information about the affect of SQL builds on LS.
Thanks
Amer M J
MCP
"DavidCur" wrote:
> Hi,
> I've got a production server log shipping a 280GB database to a remote DR
> site server. It has been running without incident for months now, but last
> week it stopped restoring logs (the copy process was still running) with an
> error 3456:
> [Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Could not redo log record (500478:68225:4), for
> transaction ID (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5).
> Page: LSN = (500478:57038:10), type = 2. Log: OpCode = 2, context 3,
> PrevPageLSN: (500478:68221:4).
> I restored the remote database from a full tape backup shipped to the site
> via courier (because we weren't sure if the link was to blame for the problem
> restoring). Log shipping worked well again for a few days, but this morning
> has again stopped restoring with the exact same error!
> I've read the article "http://support.microsoft.com/kb/831950", and although
> it describes the same error, it doesnt seem to apply to us as we weren't
> doing any role changing, and weren't backing up the database manually with
> the NORECOVERY switch (i.e. the backups were being done as usual by the log
> shipping maint. plan).
> Local server build is 8.00.997, and remote (DR) server build is 8.00.818.
> Could the fact that they are slightly different versions have anything to do
> with this problem?
> Could this be corruption introducted by the network link? If so, is the
> only way to fix this to fully restore the database again? Or is there some
> way to get good copies of the log it failed on and restore those manually? I
> don't have a huge amount of experience with log shipping, so any help would
> be greatly appreciated - especially being a 24/7 mission critical DR server,
> and in the middle of the holidays! Murphy's Law!
>
> Thanks,
> david|||Thanks for the quick response. The log files are copied and restored every
15 minutes. The problem occurred this morning around 12:15am. I've tried
your suggestion about restoring manually with the standby undo file.
I ran the following command (using the 12:00am file) successfully, but the
12:15am file produces the following output:
---
restore log JDE_Prod
from disk = 'K:\Backups\DRLogsIn\JDE_Prod_tlog_200512300015.TRN'
with standby = 'K:\Backups\DRLogsIn\LogUndo.tuf'
---
Deleting database file 'K:\Backups\DRLogsIn\LogUndo.tuf'.
Processed 34415 pages for database 'JDE_Prod', file 'JDE_PRODUCTION_log' on
file 1.
Server: Msg 3456, Level 21, State 1, Line 1
Could not redo log record (500478:68225:4), for transaction ID
(3:1085081790), on page (3:2789016), database 'JDE_Prod' (5). Page: LSN =(500478:57038:10), type = 2. Log: OpCode = 2, context 3, PrevPageLSN:
(500478:68221:4).
Connection Broken
---
This 12:15 file has already been re-copied, but I will try again.
Would be interesting to see if you find any issues with different builds in
log shipping. The patches were applied to the local server a few months ago
(3 or 4 months), and log shipping has been running without incident this
whole time.
Thanks again,
Dave
"Amer M J" wrote:
> Hi David
> The SQL Builds may cause a problem. I am not sure about it.
> Rearding the file restore; it isn't necessary that you restore the entire
> db. I would suggest the following steps:
> 1) Check the table msdb..log_shipping_plan_history for the last loaded file.
> try
> select * from msdb..log_shipping_plan_history order by endtime desc
> 2) Try restoring tha tfile manually from the Query Analyzer. try
> restore log
> <db_name>
> from
> disk = 'file_path'
> with
> standby = 'undo.txt'
> 3) if the above step succeeds keep restoring the successive trn files till
> the step fails. Then that file at which the step fails is the corrupt file.
> 4) Copy only that file from the primary server and try step 2 with it.
> 5) re-run the LS Jobs and the ywill succeed.
> Hope this helps.
> I shall get back with mpre information about the affect of SQL builds on LS.
> Thanks
> Amer M J
> MCP|||Hi Dave
I am curious here. Was the '.tuf' file deleted as per a part of the process
of manually ?
Also the builds do play a major role here. From what I can see as per your
information, the primary server is of a higher build than the secondary
server. So I was wondering how a log file of a db from a higher build was
getting restored onto a lower build server.
Also I would suggest checking out the integrity of the trn files on the
primary server. try
restore verifyonly command to check the backup set's integrity.
Please do check if anyother process is accessing the db on the secondary
server as this may disrupt the LS process.
Also check this link.
http://support.microsoft.com/kb/329487/en-us
Thanks
Amer M J
MCP
"DavidCur" wrote:
> Thanks for the quick response. The log files are copied and restored every
> 15 minutes. The problem occurred this morning around 12:15am. I've tried
> your suggestion about restoring manually with the standby undo file.
> I ran the following command (using the 12:00am file) successfully, but the
> 12:15am file produces the following output:
> ---
> restore log JDE_Prod
> from disk = 'K:\Backups\DRLogsIn\JDE_Prod_tlog_200512300015.TRN'
> with standby = 'K:\Backups\DRLogsIn\LogUndo.tuf'
> ---
> Deleting database file 'K:\Backups\DRLogsIn\LogUndo.tuf'.
> Processed 34415 pages for database 'JDE_Prod', file 'JDE_PRODUCTION_log' on
> file 1.
> Server: Msg 3456, Level 21, State 1, Line 1
> Could not redo log record (500478:68225:4), for transaction ID
> (3:1085081790), on page (3:2789016), database 'JDE_Prod' (5). Page: LSN => (500478:57038:10), type = 2. Log: OpCode = 2, context 3, PrevPageLSN:
> (500478:68221:4).
> Connection Broken
> ---
> This 12:15 file has already been re-copied, but I will try again.
> Would be interesting to see if you find any issues with different builds in
> log shipping. The patches were applied to the local server a few months ago
> (3 or 4 months), and log shipping has been running without incident this
> whole time.
> Thanks again,
> Dave
>
> "Amer M J" wrote:
> > Hi David
> >
> > The SQL Builds may cause a problem. I am not sure about it.
> >
> > Rearding the file restore; it isn't necessary that you restore the entire
> > db. I would suggest the following steps:
> >
> > 1) Check the table msdb..log_shipping_plan_history for the last loaded file.
> > try
> >
> > select * from msdb..log_shipping_plan_history order by endtime desc
> >
> > 2) Try restoring tha tfile manually from the Query Analyzer. try
> >
> > restore log
> > <db_name>
> > from
> > disk = 'file_path'
> > with
> > standby = 'undo.txt'
> >
> > 3) if the above step succeeds keep restoring the successive trn files till
> > the step fails. Then that file at which the step fails is the corrupt file.
> >
> > 4) Copy only that file from the primary server and try step 2 with it.
> >
> > 5) re-run the LS Jobs and the ywill succeed.
> >
> > Hope this helps.
> >
> > I shall get back with mpre information about the affect of SQL builds on LS.
> >
> > Thanks
> > Amer M J
> > MCP|||Hi again,
Yes, the standby file (whatever it has been called) is automatically deleted
by the restore process.
Good news though, I seem to have log shipping going again! :-)
I re-copied the 12:15am log file (yet again, 3rd time) and restored it with
the same syntax as in my previous post, and it worked. So the problem must
lie with our link to the remote DR server. Its now been logged to the
telecom company who provide the WAN pipe.
As a precautionary measure I will schedule the remote server to be patched
to the same build level as our local server (will be next year though as we
are in a "holiday change freeze" now).
Funnily enough, the restore headeronly, verifyonly and filelistonly all
seemed to work fine with the corrupt file. Is it possible that the header of
the file was okay, while the actual data was bad'
Thanks very much for the help though, and I will update with anything new we
find.
Dave
"Amer M J" wrote:
> Hi Dave
> I am curious here. Was the '.tuf' file deleted as per a part of the process
> of manually ?
> Also the builds do play a major role here. From what I can see as per your
> information, the primary server is of a higher build than the secondary
> server. So I was wondering how a log file of a db from a higher build was
> getting restored onto a lower build server.
> Also I would suggest checking out the integrity of the trn files on the
> primary server. try
> restore verifyonly command to check the backup set's integrity.
> Please do check if anyother process is accessing the db on the secondary
> server as this may disrupt the LS process.
> Also check this link.
> http://support.microsoft.com/kb/329487/en-us
> Thanks
> Amer M J
> MCP
Monday, March 12, 2012
Log Shipping going out-of-sync
Hello everyone:
I wanted to ask a question about log shipping going out of sync. We have a remote server in the UK that is our production server, and we are trying to set up log shipping with another server in the US. Our Log shipping monitor goes "out-of-sync" after about 8-10 hours. We changed the US server to have the same time as the server in the UK, but there still seems to be an out of syn error after 8 hours.
We are using Windows server 2000 and SQL server 2000.
SQL server will restore logs then after a few hours it stops restoring and goes out of sync. Was just wondering if someone knew the possibilities of why.
Thank you.
Hi Bendoke,
I can see your direction to solve the issue sicne the local time difference between UK and US is 8 hours. does the outofsync occurs exactly after 8 hours LS got setup ?
once the outofsync occurs, have you checked the backup/copy/restore job history to see if there is any error ?
regards!
Yunwen
|||No, the out of sync error occurs 45 minutes after beginning log shipping. When I view the backup/copy/restore history, everything is "successful" I am thinking that the monitor may not be reading something correctly. Someone also mentioned that my anti-virus scan may interfere with this? I do not know if this is accurate or not.
Thank you,
Ben
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)