Log shipping alerts: sync

Showing posts with label sync. Show all posts

Friday, March 30, 2012

Log shipping using Enterpirse Ed. Monitor DB getting out of sync

We are testing the canned log shipping built into sqlserver2k ent. Somehow,
the monitor db that tracks what has been backed-up, shipped, etc is out of
sync. The primary and secondary DBs are synced up and the shipping is
working fine but the monitor doesnt seem to know that. We suspect we know
what happened (sqlAgent was stopped when log shipping was started) but this
raises a bigger question.
If the monitor db should get out of sync (say, for example, it is stopped
for some time), how to we resync it? Conceptually, we want to tell it that
the primary and secondary are in sync (which we can separately verify). Any
ideas? Is this even the right news group?
-alanNever mind. It was an NT domain security issue. We just needed to make the
userName match for all the services (sqlserver and agent) on all the
machines.
"Alan Berezin" <aberezin@.drillinginfo.com> wrote in message
news:eEjNKJkRDHA.1924@.TK2MSFTNGP12.phx.gbl...
> We are testing the canned log shipping built into sqlserver2k ent.
Somehow,
> the monitor db that tracks what has been backed-up, shipped, etc is out of
> sync. The primary and secondary DBs are synced up and the shipping is
> working fine but the monitor doesnt seem to know that. We suspect we know
> what happened (sqlAgent was stopped when log shipping was started) but
this
> raises a bigger question.
> If the monitor db should get out of sync (say, for example, it is stopped
> for some time), how to we resync it? Conceptually, we want to tell it
that
> the primary and secondary are in sync (which we can separately verify).
Any
> ideas? Is this even the right news group?
> -alan
>sql

Wednesday, March 28, 2012

Log shipping sync error 14421

Hi
SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
broken, out of sync error for all databases on standby server error
14421. I have manually restored all the log backups and updated manually
log_shipping_secondaries columns set last_copied_last_updated &
last_loaded_last_updated with 15 min difference which i have configured.
But the log backups are not copied &last_copied_filename is not getting
updated. how can i fix this issues and continue log shipping with out
reconfiguring?
thanks for looking
kris
Did you look at:
Description of error message 14420 and error message 14421 that occur when
you use log shipping in SQL Server
http://support.microsoft.com/kb/329133
?
"krishna" wrote:

> Hi
> SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
> broken, out of sync error for all databases on standby server error
> 14421. I have manually restored all the log backups and updated manually
> log_shipping_secondaries columns set last_copied_last_updated &
> last_loaded_last_updated with 15 min difference which i have configured.
> But the log backups are not copied &last_copied_filename is not getting
> updated. how can i fix this issues and continue log shipping with out
> reconfiguring?
> thanks for looking
> kris
>
|||I looked into that kb article, i have tried accessing the folder on the
standby server from source machine. it is fine. i dont know why the logs
are not copied to standby server. I just want to fix the issue without
reconfiguring. Is there any way to copy & restore the remaining logs and
updating the logshipping tables and continue the process?
Edgardo Valdez wrote:[vbcol=seagreen]
> Did you look at:
> Description of error message 14420 and error message 14421 that occur when
> you use log shipping in SQL Server
> http://support.microsoft.com/kb/329133
> ?
> "krishna" wrote:
|||I looked into that kb article, i have tried accessing the folder on the
standby server from source machine. it is fine. i dont know why the logs
are not copied to standby server. I just want to fix the issue without
reconfiguring. Is there any way to copy & restore the remaining logs and
updating the logshipping tables and continue the process?
krishna wrote:
> Hi
> SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
> broken, out of sync error for all databases on standby server error
> 14421. I have manually restored all the log backups and updated manually
> log_shipping_secondaries columns set last_copied_last_updated &
> last_loaded_last_updated with 15 min difference which i have configured.
> But the log backups are not copied &last_copied_filename is not getting
> updated. how can i fix this issues and continue log shipping with out
> reconfiguring?
> thanks for looking
> kris

Log shipping sync error 14421

Hi
SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
broken, out of sync error for all databases on standby server error
14421. I have manually restored all the log backups and updated manually
log_shipping_secondaries columns set last_copied_last_updated &
last_loaded_last_updated with 15 min difference which i have configured.
But the log backups are not copied &last_copied_filename is not getting
updated. how can i fix this issues and continue log shipping with out
reconfiguring?
thanks for looking
krisDid you look at:
Description of error message 14420 and error message 14421 that occur when
you use log shipping in SQL Server
http://support.microsoft.com/kb/329133
?
"krishna" wrote:

> Hi
> SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
> broken, out of sync error for all databases on standby server error
> 14421. I have manually restored all the log backups and updated manually
> log_shipping_secondaries columns set last_copied_last_updated &
> last_loaded_last_updated with 15 min difference which i have configured.
> But the log backups are not copied &last_copied_filename is not getting
> updated. how can i fix this issues and continue log shipping with out
> reconfiguring?
> thanks for looking
> kris
>|||I looked into that kb article, i have tried accessing the folder on the
standby server from source machine. it is fine. i dont know why the logs
are not copied to standby server. I just want to fix the issue without
reconfiguring. Is there any way to copy & restore the remaining logs and
updating the logshipping tables and continue the process?
Edgardo Valdez wrote:[vbcol=seagreen]
> Did you look at:
> Description of error message 14420 and error message 14421 that occur when
> you use log shipping in SQL Server
> http://support.microsoft.com/kb/329133
> ?
> "krishna" wrote:
>|||I looked into that kb article, i have tried accessing the folder on the
standby server from source machine. it is fine. i dont know why the logs
are not copied to standby server. I just want to fix the issue without
reconfiguring. Is there any way to copy & restore the remaining logs and
updating the logshipping tables and continue the process?
krishna wrote:
> Hi
> SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
> broken, out of sync error for all databases on standby server error
> 14421. I have manually restored all the log backups and updated manually
> log_shipping_secondaries columns set last_copied_last_updated &
> last_loaded_last_updated with 15 min difference which i have configured.
> But the log backups are not copied &last_copied_filename is not getting
> updated. how can i fix this issues and continue log shipping with out
> reconfiguring?
> thanks for looking
> kris

Log shipping sync error 14421

Hi
SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
broken, out of sync error for all databases on standby server error
14421. I have manually restored all the log backups and updated manually
log_shipping_secondaries columns set last_copied_last_updated &
last_loaded_last_updated with 15 min difference which i have configured.
But the log backups are not copied &last_copied_filename is not getting
updated. how can i fix this issues and continue log shipping with out
reconfiguring?
thanks for looking
krisDid you look at:
Description of error message 14420 and error message 14421 that occur when
you use log shipping in SQL Server
http://support.microsoft.com/kb/329133
?
"krishna" wrote:
> Hi
> SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
> broken, out of sync error for all databases on standby server error
> 14421. I have manually restored all the log backups and updated manually
> log_shipping_secondaries columns set last_copied_last_updated &
> last_loaded_last_updated with 15 min difference which i have configured.
> But the log backups are not copied &last_copied_filename is not getting
> updated. how can i fix this issues and continue log shipping with out
> reconfiguring?
> thanks for looking
> kris
>|||I looked into that kb article, i have tried accessing the folder on the
standby server from source machine. it is fine. i dont know why the logs
are not copied to standby server. I just want to fix the issue without
reconfiguring. Is there any way to copy & restore the remaining logs and
updating the logshipping tables and continue the process?
Edgardo Valdez wrote:
> Did you look at:
> Description of error message 14420 and error message 14421 that occur when
> you use log shipping in SQL Server
> http://support.microsoft.com/kb/329133
> ?
> "krishna" wrote:
>> Hi
>> SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
>> broken, out of sync error for all databases on standby server error
>> 14421. I have manually restored all the log backups and updated manually
>> log_shipping_secondaries columns set last_copied_last_updated &
>> last_loaded_last_updated with 15 min difference which i have configured.
>> But the log backups are not copied &last_copied_filename is not getting
>> updated. how can i fix this issues and continue log shipping with out
>> reconfiguring?
>> thanks for looking
>> kris|||I looked into that kb article, i have tried accessing the folder on the
standby server from source machine. it is fine. i dont know why the logs
are not copied to standby server. I just want to fix the issue without
reconfiguring. Is there any way to copy & restore the remaining logs and
updating the logshipping tables and continue the process?
krishna wrote:
> Hi
> SQLServer 2000 sp3 enterprise running logshipping smoothly, today its
> broken, out of sync error for all databases on standby server error
> 14421. I have manually restored all the log backups and updated manually
> log_shipping_secondaries columns set last_copied_last_updated &
> last_loaded_last_updated with 15 min difference which i have configured.
> But the log backups are not copied &last_copied_filename is not getting
> updated. how can i fix this issues and continue log shipping with out
> reconfiguring?
> thanks for looking
> kris

Wednesday, March 21, 2012

Log Shipping out of sync

Hi everyone,
We have set up a log shipping between two SQL 2000 servers with a monitor
server as well.
The problem is that I've been on holidays, and the people that should check
the databases didn't do it. The result is that the log shipping has been out
ot sync since 18th april till now.
We get the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL
Server Driver][SQL Server]The file on device
'C:\xx\LogShipIN\TRNs\xx_tlog_200704180300.TRN' is not a valid Microsoft Tape
Format backup set.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
I've check the file and already exist in backup server, but it seems it
wasn't copied correctly. Because it's a lot of days since the file fail to
restore (it's from 18 th april), we don't have it on production server (we
have only from 2 days ago).
Does anybody knows how could I re-sync the log shipping on backup server
again?
Thansk a lot,
Alex.You need to recreate the Log Shipping Maintenance plan for the desired
database.
In order to deleted the current plan, you'll need to remove LS from the plan
first.
If the latest full db backup that you have was created after the error
occurred and you still have all of the log (and/or differential) backups
since the latest db backup then you can use these files to reinitialize db on
the secondary server instead of having to take a full backup of the db. This
would allow you to set up log shipping without having to impact performance
on your primary db.
check out these articles
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
MG
"Alex Delgado" wrote:
> Hi everyone,
> We have set up a log shipping between two SQL 2000 servers with a monitor
> server as well.
> The problem is that I've been on holidays, and the people that should check
> the databases didn't do it. The result is that the log shipping has been out
> ot sync since 18th april till now.
> We get the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL
> Server Driver][SQL Server]The file on device
> 'C:\xx\LogShipIN\TRNs\xx_tlog_200704180300.TRN' is not a valid Microsoft Tape
> Format backup set.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> abnormally.
> I've check the file and already exist in backup server, but it seems it
> wasn't copied correctly. Because it's a lot of days since the file fail to
> restore (it's from 18 th april), we don't have it on production server (we
> have only from 2 days ago).
> Does anybody knows how could I re-sync the log shipping on backup server
> again?
> Thansk a lot,
> Alex.|||Hi Hurme,
Thanks a lot for your help.
I still have a doubt.
When you say:
" If the latest full db backup that you have was created after the error
occurred and you still have all of the log (and/or differential) backups
since the latest db backup then you can use these files to reinitialize db on
the secondary server instead of having to take a full backup of the db. This
would allow you to set up log shipping without having to impact performance
on your primary db. "
I have full db backup created after the error that we can restore on
secondary server. About the log backups, I understand you mean the trn files
that log shipping copies to secondary server and that it tries to restore,
don't you? If so, then my answer is yes, I also have them.
Should I run the restore with the following parameter: restore
"Hurme" wrote:
> You need to recreate the Log Shipping Maintenance plan for the desired
> database.
> In order to deleted the current plan, you'll need to remove LS from the plan
> first.
> If the latest full db backup that you have was created after the error
> occurred and you still have all of the log (and/or differential) backups
> since the latest db backup then you can use these files to reinitialize db on
> the secondary server instead of having to take a full backup of the db. This
> would allow you to set up log shipping without having to impact performance
> on your primary db.
> check out these articles
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
> http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
>
> --
> MG
>
> "Alex Delgado" wrote:
> > Hi everyone,
> >
> > We have set up a log shipping between two SQL 2000 servers with a monitor
> > server as well.
> >
> > The problem is that I've been on holidays, and the people that should check
> > the databases didn't do it. The result is that the log shipping has been out
> > ot sync since 18th april till now.
> >
> > We get the following error:
> >
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL
> > Server Driver][SQL Server]The file on device
> > 'C:\xx\LogShipIN\TRNs\xx_tlog_200704180300.TRN' is not a valid Microsoft Tape
> > Format backup set.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> > abnormally.
> >
> > I've check the file and already exist in backup server, but it seems it
> > wasn't copied correctly. Because it's a lot of days since the file fail to
> > restore (it's from 18 th april), we don't have it on production server (we
> > have only from 2 days ago).
> >
> > Does anybody knows how could I re-sync the log shipping on backup server
> > again?
> >
> > Thansk a lot,
> >
> > Alex.|||Hi Hurme,
Thanks a lot for your help.
I still have a doubt.
When you say:
" If the latest full db backup that you have was created after the error
occurred and you still have all of the log (and/or differential) backups
since the latest db backup then you can use these files to reinitialize db on
the secondary server instead of having to take a full backup of the db. This
would allow you to set up log shipping without having to impact performance
on your primary db. "
I have full db backup created after the error that we can restore on
secondary server. About the log backups, I understand you mean the trn files
that log shipping copies to secondary server and that it tries to restore,
don't you? If so, then my answer is yes, I also have them.
Should I run the restore with the following parameter: restore with standby'
How should be the exact command to restore?
Thanks again,
Alex.
"Hurme" wrote:
> You need to recreate the Log Shipping Maintenance plan for the desired
> database.
> In order to deleted the current plan, you'll need to remove LS from the plan
> first.
> If the latest full db backup that you have was created after the error
> occurred and you still have all of the log (and/or differential) backups
> since the latest db backup then you can use these files to reinitialize db on
> the secondary server instead of having to take a full backup of the db. This
> would allow you to set up log shipping without having to impact performance
> on your primary db.
> check out these articles
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
> http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
>
> --
> MG
>
> "Alex Delgado" wrote:
> > Hi everyone,
> >
> > We have set up a log shipping between two SQL 2000 servers with a monitor
> > server as well.
> >
> > The problem is that I've been on holidays, and the people that should check
> > the databases didn't do it. The result is that the log shipping has been out
> > ot sync since 18th april till now.
> >
> > We get the following error:
> >
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL
> > Server Driver][SQL Server]The file on device
> > 'C:\xx\LogShipIN\TRNs\xx_tlog_200704180300.TRN' is not a valid Microsoft Tape
> > Format backup set.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> > abnormally.
> >
> > I've check the file and already exist in backup server, but it seems it
> > wasn't copied correctly. Because it's a lot of days since the file fail to
> > restore (it's from 18 th april), we don't have it on production server (we
> > have only from 2 days ago).
> >
> > Does anybody knows how could I re-sync the log shipping on backup server
> > again?
> >
> > Thansk a lot,
> >
> > Alex.|||Yes, by log I meant the .trn files.
If you don't use standby, the backup database will be left offline and not
readable so that successive .trn (backup log) files can be restored.
If you restore with standby the backup db will be accessable in Read-Only
mode and still be able to have successive .trn files restored to it. The only
issue with this is that exclussive access to the db is still need to restore
a .trn so if some one is accessing the db they need to be kicked out of the
db before a restore can happen.
The restore with standby command should look like:
restore database <db_name>
from disk = 'c:\<backup>.BAK'
with standby = <undo_file_name>
likewise for .trn files
restore log <db_name>
from disk = 'c:\<log_backup>.trn'
with standby = <undo_file_name>
If you're not using it, try using Enterprise Manager to set up Log Shipping.
--
MG
"Alex Delgado" wrote:
> Hi Hurme,
> Thanks a lot for your help.
> I still have a doubt.
> When you say:
> " If the latest full db backup that you have was created after the error
> occurred and you still have all of the log (and/or differential) backups
> since the latest db backup then you can use these files to reinitialize db on
> the secondary server instead of having to take a full backup of the db. This
> would allow you to set up log shipping without having to impact performance
> on your primary db. "
> I have full db backup created after the error that we can restore on
> secondary server. About the log backups, I understand you mean the trn files
> that log shipping copies to secondary server and that it tries to restore,
> don't you? If so, then my answer is yes, I also have them.
> Should I run the restore with the following parameter: restore with standby'
> How should be the exact command to restore?
> Thanks again,
> Alex.
>
> "Hurme" wrote:
> > You need to recreate the Log Shipping Maintenance plan for the desired
> > database.
> > In order to deleted the current plan, you'll need to remove LS from the plan
> > first.
> >
> > If the latest full db backup that you have was created after the error
> > occurred and you still have all of the log (and/or differential) backups
> > since the latest db backup then you can use these files to reinitialize db on
> > the secondary server instead of having to take a full backup of the db. This
> > would allow you to set up log shipping without having to impact performance
> > on your primary db.
> >
> > check out these articles
> > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
> >
> > http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
> >
> >
> > --
> > MG
> >
> >
> > "Alex Delgado" wrote:
> >
> > > Hi everyone,
> > >
> > > We have set up a log shipping between two SQL 2000 servers with a monitor
> > > server as well.
> > >
> > > The problem is that I've been on holidays, and the people that should check
> > > the databases didn't do it. The result is that the log shipping has been out
> > > ot sync since 18th april till now.
> > >
> > > We get the following error:
> > >
> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL
> > > Server Driver][SQL Server]The file on device
> > > 'C:\xx\LogShipIN\TRNs\xx_tlog_200704180300.TRN' is not a valid Microsoft Tape
> > > Format backup set.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> > > abnormally.
> > >
> > > I've check the file and already exist in backup server, but it seems it
> > > wasn't copied correctly. Because it's a lot of days since the file fail to
> > > restore (it's from 18 th april), we don't have it on production server (we
> > > have only from 2 days ago).
> > >
> > > Does anybody knows how could I re-sync the log shipping on backup server
> > > again?
> > >
> > > Thansk a lot,
> > >
> > > Alex.|||On Apr 24, 1:50 pm, Alex Delgado
<AlexDelg...@.discussions.microsoft.com> wrote:
> Hi everyone,
> We have set up a log shipping between two SQL 2000 servers with a monitor
> server as well.
> The problem is that I've been on holidays, and the people that should check
> the databases didn't do it. The result is that the log shipping has been out
> ot sync since 18th april till now.
> We get the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL
> Server Driver][SQL Server]The file on device
> 'C:\xx\LogShipIN\TRNs\xx_tlog_200704180300.TRN' is not a valid Microsoft Tape
> Format backup set.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> abnormally.
> I've check the file and already exist in backup server, but it seems it
> wasn't copied correctly. Because it's a lot of days since the file fail to
> restore (it's from 18 th april), we don't have it on production server (we
> have only from 2 days ago).
> Does anybody knows how could I re-sync the log shipping on backup server
> again?
> Thansk a lot,
> Alex.
You should have valid backup. If backup on tape is not O.K then you
can not restore next subsequent log backups. You have to setup
logshipping again,
Regards
Amish Shah
http://shahamishm.tripod.com|||Hi Hurme,
Thanks againg for your help.
I still have a doubt. Before performing the restore on secondary server,
should I disable/delete logshipping, or could I restore the database on
secondary without disabling/deleting logshipping?
If I had to disable/delete logshipping, should I delete the trn log files
that are on secondary and that have been copied before recreating the
logshipping? I understand yes.
I had to restore with standby, because there are users that access this
database in read mode on secondary server.
Thanks,
Alex.
"Hurme" wrote:
> Yes, by log I meant the .trn files.
> If you don't use standby, the backup database will be left offline and not
> readable so that successive .trn (backup log) files can be restored.
> If you restore with standby the backup db will be accessable in Read-Only
> mode and still be able to have successive .trn files restored to it. The only
> issue with this is that exclussive access to the db is still need to restore
> a .trn so if some one is accessing the db they need to be kicked out of the
> db before a restore can happen.
> The restore with standby command should look like:
> restore database <db_name>
> from disk = 'c:\<backup>.BAK'
> with standby = <undo_file_name>
> likewise for .trn files
> restore log <db_name>
> from disk = 'c:\<log_backup>.trn'
> with standby = <undo_file_name>
> If you're not using it, try using Enterprise Manager to set up Log Shipping.
> --
> MG
>
> "Alex Delgado" wrote:
> > Hi Hurme,
> >
> > Thanks a lot for your help.
> >
> > I still have a doubt.
> >
> > When you say:
> >
> > " If the latest full db backup that you have was created after the error
> > occurred and you still have all of the log (and/or differential) backups
> > since the latest db backup then you can use these files to reinitialize db on
> > the secondary server instead of having to take a full backup of the db. This
> > would allow you to set up log shipping without having to impact performance
> > on your primary db. "
> >
> > I have full db backup created after the error that we can restore on
> > secondary server. About the log backups, I understand you mean the trn files
> > that log shipping copies to secondary server and that it tries to restore,
> > don't you? If so, then my answer is yes, I also have them.
> >
> > Should I run the restore with the following parameter: restore with standby'
> >
> > How should be the exact command to restore?
> >
> > Thanks again,
> >
> > Alex.
> >
> >
> > "Hurme" wrote:
> >
> > > You need to recreate the Log Shipping Maintenance plan for the desired
> > > database.
> > > In order to deleted the current plan, you'll need to remove LS from the plan
> > > first.
> > >
> > > If the latest full db backup that you have was created after the error
> > > occurred and you still have all of the log (and/or differential) backups
> > > since the latest db backup then you can use these files to reinitialize db on
> > > the secondary server instead of having to take a full backup of the db. This
> > > would allow you to set up log shipping without having to impact performance
> > > on your primary db.
> > >
> > > check out these articles
> > > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
> > >
> > > http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
> > >
> > >
> > > --
> > > MG
> > >
> > >
> > > "Alex Delgado" wrote:
> > >
> > > > Hi everyone,
> > > >
> > > > We have set up a log shipping between two SQL 2000 servers with a monitor
> > > > server as well.
> > > >
> > > > The problem is that I've been on holidays, and the people that should check
> > > > the databases didn't do it. The result is that the log shipping has been out
> > > > ot sync since 18th april till now.
> > > >
> > > > We get the following error:
> > > >
> > > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL
> > > > Server Driver][SQL Server]The file on device
> > > > 'C:\xx\LogShipIN\TRNs\xx_tlog_200704180300.TRN' is not a valid Microsoft Tape
> > > > Format backup set.
> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> > > > abnormally.
> > > >
> > > > I've check the file and already exist in backup server, but it seems it
> > > > wasn't copied correctly. Because it's a lot of days since the file fail to
> > > > restore (it's from 18 th april), we don't have it on production server (we
> > > > have only from 2 days ago).
> > > >
> > > > Does anybody knows how could I re-sync the log shipping on backup server
> > > > again?
> > > >
> > > > Thansk a lot,
> > > >
> > > > Alex.

log shipping out of sync

I have set up a database with log shipping to the backup server. I have actually set it up multiple times because it continues to get out of sync, and I do not know another way to get it back in sync (despite much effort to learn how). I have not found much documentation in BOL, nor in the on-line areas that I can find. I have not been able to discover why it keeps getting out of sync.
If there is anyone who may shed some light on this, I would be very greatful.
Particulars (both servers):
Windows ADV Server 2003
SQL 2000 (Enterprise Ed.)
Servers on a work group (not domain)

There is a separate database on the same servers that continues to have successful log shipping.
Thank you very much (in advance).

you can try to manually restore the logs that was not applied to the secondary database, or, restore the latest full backup, and then re-sume logshipping to get your logshipping work again.

Is there anything particular on this configuration? you mentioned that Logshipping with other databases worked successfully on the same server so I assume your configuration of both LS are correct.

Thanks

Yunwen

Log Shipping out of sync

You need to recreate the Log Shipping Maintenance plan for the desired
database.
In order to deleted the current plan, you'll need to remove LS from the plan
first.
If the latest full db backup that you have was created after the error
occurred and you still have all of the log (and/or differential) backups
since the latest db backup then you can use these files to reinitialize db on
the secondary server instead of having to take a full backup of the db. This
would allow you to set up log shipping without having to impact performance
on your primary db.
check out these articles
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
MG
"Alex Delgado" wrote:

> Hi everyone,
> We have set up a log shipping between two SQL 2000 servers with a monitor
> server as well.
> The problem is that I've been on holidays, and the people that should check
> the databases didn't do it. The result is that the log shipping has been out
> ot sync since 18th april till now.
> We get the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL
> Server Driver][SQL Server]The file on device
> 'C:\xx\LogShipIN\TRNs\xx_tlog_200704180300.TRN' is not a valid Microsoft Tape
> Format backup set.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> abnormally.
> I've check the file and already exist in backup server, but it seems it
> wasn't copied correctly. Because it's a lot of days since the file fail to
> restore (it's from 18 th april), we don't have it on production server (we
> have only from 2 days ago).
> Does anybody knows how could I re-sync the log shipping on backup server
> again?
> Thansk a lot,
> Alex.
Hi Hurme,
Thanks a lot for your help.
I still have a doubt.
When you say:
" If the latest full db backup that you have was created after the error
occurred and you still have all of the log (and/or differential) backups
since the latest db backup then you can use these files to reinitialize db on
the secondary server instead of having to take a full backup of the db. This
would allow you to set up log shipping without having to impact performance
on your primary db. "
I have full db backup created after the error that we can restore on
secondary server. About the log backups, I understand you mean the trn files
that log shipping copies to secondary server and that it tries to restore,
don't you? If so, then my answer is yes, I also have them.
Should I run the restore with the following parameter: restore
"Hurme" wrote:
[vbcol=seagreen]
> You need to recreate the Log Shipping Maintenance plan for the desired
> database.
> In order to deleted the current plan, you'll need to remove LS from the plan
> first.
> If the latest full db backup that you have was created after the error
> occurred and you still have all of the log (and/or differential) backups
> since the latest db backup then you can use these files to reinitialize db on
> the secondary server instead of having to take a full backup of the db. This
> would allow you to set up log shipping without having to impact performance
> on your primary db.
> check out these articles
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
> http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
>
> --
> MG
>
> "Alex Delgado" wrote:
|||Hi Hurme,
Thanks a lot for your help.
I still have a doubt.
When you say:
" If the latest full db backup that you have was created after the error
occurred and you still have all of the log (and/or differential) backups
since the latest db backup then you can use these files to reinitialize db on
the secondary server instead of having to take a full backup of the db. This
would allow you to set up log shipping without having to impact performance
on your primary db. "
I have full db backup created after the error that we can restore on
secondary server. About the log backups, I understand you mean the trn files
that log shipping copies to secondary server and that it tries to restore,
don't you? If so, then my answer is yes, I also have them.
Should I run the restore with the following parameter: restore with standby?
How should be the exact command to restore?
Thanks again,
Alex.
"Hurme" wrote:
[vbcol=seagreen]
> You need to recreate the Log Shipping Maintenance plan for the desired
> database.
> In order to deleted the current plan, you'll need to remove LS from the plan
> first.
> If the latest full db backup that you have was created after the error
> occurred and you still have all of the log (and/or differential) backups
> since the latest db backup then you can use these files to reinitialize db on
> the secondary server instead of having to take a full backup of the db. This
> would allow you to set up log shipping without having to impact performance
> on your primary db.
> check out these articles
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
> http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
>
> --
> MG
>
> "Alex Delgado" wrote:
|||Yes, by log I meant the .trn files.
If you don't use standby, the backup database will be left offline and not
readable so that successive .trn (backup log) files can be restored.
If you restore with standby the backup db will be accessable in Read-Only
mode and still be able to have successive .trn files restored to it. The only
issue with this is that exclussive access to the db is still need to restore
a .trn so if some one is accessing the db they need to be kicked out of the
db before a restore can happen.
The restore with standby command should look like:
restore database <db_name>
from disk = 'c:\<backup>.BAK'
with standby = <undo_file_name>
likewise for .trn files
restore log <db_name>
from disk = 'c:\<log_backup>.trn'
with standby = <undo_file_name>
If you're not using it, try using Enterprise Manager to set up Log Shipping.
MG
"Alex Delgado" wrote:
[vbcol=seagreen]
> Hi Hurme,
> Thanks a lot for your help.
> I still have a doubt.
> When you say:
> " If the latest full db backup that you have was created after the error
> occurred and you still have all of the log (and/or differential) backups
> since the latest db backup then you can use these files to reinitialize db on
> the secondary server instead of having to take a full backup of the db. This
> would allow you to set up log shipping without having to impact performance
> on your primary db. "
> I have full db backup created after the error that we can restore on
> secondary server. About the log backups, I understand you mean the trn files
> that log shipping copies to secondary server and that it tries to restore,
> don't you? If so, then my answer is yes, I also have them.
> Should I run the restore with the following parameter: restore with standby?
> How should be the exact command to restore?
> Thanks again,
> Alex.
>
> "Hurme" wrote:
sql

Log Shipping out of sync

log shipping not working

hello, on the destination server he says the restore job has failed
and my log shipping databases are then out of sync. where do i begin
to find out why my log shipping is no longer working? my two servers
are in differnet locations but i can ping and returns are under 50ms.
help?
thanks
APAP
Is it one log it has failed to apply or several? Sometimes
one log may fail to apply for a number of reasons. Next
time the target server tries to apply the logs it should
try to apply all logs since it last applied one. Quite
often it can catch up.
One common reason for log shipping failure is having the
restore on the target run too soon after the backup on the
source. Occasionally the restore tries to run before the
backup finishes. This may be because a big work load on
the source has caused a larger than normal log. It may be
the servers have got out of sync time wise.
Hope this helps
John|||thanks you john, it looks to me that the log shipping has never worked
since the backup was restored in the destincation server. these is a
small database like 63 mb so backup times is quick. each site has a
t1 or faster.
how do i get them back to sync?
AP
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message news:<0ec801c3aa02$82d32cc0$a401280a@.phx.gbl>...
> AP
> Is it one log it has failed to apply or several? Sometimes
> one log may fail to apply for a number of reasons. Next
> time the target server tries to apply the logs it should
> try to apply all logs since it last applied one. Quite
> often it can catch up.
> One common reason for log shipping failure is having the
> restore on the target run too soon after the backup on the
> source. Occasionally the restore tries to run before the
> backup finishes. This may be because a big work load on
> the source has caused a larger than normal log. It may be
> the servers have got out of sync time wise.
> Hope this helps
> John|||Antonio
You will need to copy a recent full backup to your standby
server, to re-establish the log shipping.
I don't have a copy of Enterprise edition that I can look
at at the moment to see exactly how you do it. There
should be an option somewhere to re-establish it.
The fact that it has never worked suggests there is a
problem anyway. Permissions on the share on the target
server is quite a common problem. Use 'xp_cmdshell' on
your source database to check the permissions work. (Do
a 'dir' on it).
Also worth having a look at this link.
http://support.microsoft.com/default.aspx?scid=kb;en-
us;323135
Hope this helps
John|||thank you again. i did check permissions. i see on teh source and
destination server where the transaction logs are that all are on both
servers so i see this to mean that the logs are baccking up on the
source server good and they are being transmisssion to the destination
server good. it looks to me that the logs are shipping ok but not
being replayed on the destination server.
this probably changes your recommentations.
what do you think about it now?
thanks
AP
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message news:<0adc01c3aac4$4e192ad0$a501280a@.phx.gbl>...
> Antonio
> You will need to copy a recent full backup to your standby
> server, to re-establish the log shipping.
> I don't have a copy of Enterprise edition that I can look
> at at the moment to see exactly how you do it. There
> should be an option somewhere to re-establish it.
> The fact that it has never worked suggests there is a
> problem anyway. Permissions on the share on the target
> server is quite a common problem. Use 'xp_cmdshell' on
> your source database to check the permissions work. (Do
> a 'dir' on it).
> Also worth having a look at this link.
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;323135
> Hope this helps
> John|||Antonio
Last thing I can think of suggesting is to try running the
SQL Server agent as a local domain account with the same
name and password on both servers.
I have seen this work.
Regards
John

Monday, March 19, 2012

Log shipping monitor our of sync SQL Server 2005

I have set up log shipping between two servers with a third serving as a monitoring server. Recently the monitoring for the eight databases that I am log shipping began to indicate that everything was failing. Upon further inventigation of the log_shipping_monitor_history_detail tables on the prinary and secondary everthing appears to working properly but I have been unable to resyncronize the monitoring. I have attempted to use the stored procedure sp_refresh_log_shipping_monitor to no avail, it does not appear to do anything with respect to the data in the dbo.log_shipping_primary_databases and log_shipping_monitor_primary tables on the primary server or the log_shipping_monitor_secondary and dbo.log_shipping_secondary_databases tables on the secondary. I have also manually updated the records in these tables in an attempt to syncronize but after the next sucessful backup, copy and restore this monitoring data is not updating. Does anyone have any ideas as to what I am doing wrong or how I might rectify this situation.Moving to availability forum.|||

I took a shot at reproducing this issue. I could not reproduce the scenario that you describe, but here is some information that may help.

1) Executing sp_refresh_log_shipping_monitor probably hurt more than it helped in this case. There is a bug in this stored procedure that is fixed in SP2 which results in the data that is supposed to be refreshed on the monitor server actually being deleted.

2) After getting my local test monitor server into a bad state, the only way to recover was to remove log shipping and then add it again. When adding it back, you could bypass initializing the secondary database because the primary and secondary are already in synch. After doing this and running through one cycle of backing up a tlog, copy, and restore the monitor server once again had current information on the log shipped database.

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

Log shipping monitor our of sync SQL Server 2005

I took a shot at reproducing this issue. I could not reproduce the scenario that you describe, but here is some information that may help.

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

Log shipping monitor our of sync SQL Server 2005

I took a shot at reproducing this issue. I could not reproduce the scenario that you describe, but here is some information that may help.

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

log shipping login sync

Hi,

I have log shipping setup working fine. I created new login (jskow) on primary server and he is a valid user in the database which is configure for log shipping, My secondary server is read-only. I want to give jskow read access on secondary, I added jskow to secondary server, log shipping copied that database user to secondary database , but when I try to login to secondary server and use the read-only database , I get error not a valid user?,

any help?

thanks in advance.

Ram.Run SP_CHANGE_USERS_LOGIN with REPORT clause and see whether the login reported as orphaned user, if so use AUTO_FIX to fix the login.

Refer to BOOKS ONLINE for more information.

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

Log shipping goes out of sync

Hi
I have two SQL 2000 boxes setup to log ship. The box being shipped to
is also the monitor
The size of the db being shipped is around 110GB - so the initial log
ship first creates and copies across the entire db (takes a while)
then begins the transaction logs.
The problem is that the log ship goes out of sync also immediately -
from testing I've managed to get the first shipped transaction log to
load, and sometimes the second, but never further as it gets out of
sync.
Things I've tried:
Changing the schedule from the default 15 minutes to 2 hours (for copy
and load)
Ensuring the log ship process doesn't clash with a routine backup
Any suggestions?
Thanks
TobyOn Feb 19, 7:40 am, "Toby" <tjbeaum...@.gmail.com> wrote:
> Hi
> I have two SQL 2000 boxes setup to log ship. The box being shipped to
> is also the monitor
> The size of the db being shipped is around 110GB - so the initial log
> ship first creates and copies across the entire db (takes a while)
> then begins the transaction logs.
> The problem is that the log ship goes out of sync also immediately -
> from testing I've managed to get the first shipped transaction log to
> load, and sometimes the second, but never further as it gets out of
> sync.
> Things I've tried:
> Changing the schedule from the default 15 minutes to 2 hours (for copy
> and load)
> Ensuring the log ship process doesn't clash with a routine backup
> Any suggestions?
> Thanks
> Toby
Please explain this further:
"Ensuring the log ship process doesn't clash with a routine backup"
Are you running transaction log backups IN ADDITION to the log
shipping process? This will break the log shipping chain. Log
shipping works by taking a backup of the transaction log and restoring
that backup onto another database. If you run your own independent
log backup, you're advancing the LSN pointer, throwing the log
shipping backups out of sync.|||Hi
In reply to your question "Are you running transaction log backups IN
ADDITION to the log[vbcol=seagreen]
> shipping process? " - the answer is yes I am, so clearly here lies the problem.[/v
bcol]
I'm running a daily log backup, truncate and shrink - which I realise
now is going to cause issues with the log shipping. However, and
forgive me if this appears trivial, but the reason for run a log
backup, truncate and shrink is to prevent the log file getting too
big, as it currently grows at the rate of 20-30gb a day. If I rely on
the log shipping process only, will this provide adequate truncating
of the log?
Thanks|||On Feb 22, 2:48 pm, "Toby" <tjbeaum...@.gmail.com> wrote:
> Hi
> In reply to your question "Are you running transaction log backups IN
> ADDITION to the log
>
> I'm running a daily log backup, truncate and shrink - which I realise
> now is going to cause issues with the log shipping. However, and
> forgive me if this appears trivial, but the reason for run a log
> backup, truncate and shrink is to prevent the log file getting too
> big, as it currently grows at the rate of 20-30gb a day. If I rely on
> the log shipping process only, will this provide adequate truncating
> of the log?
> Thanks
Couple of key things here:
1. Log backups truncate the log - the more frequently that you run a
log backup, the quicker committed transactions will get truncated, and
the less likely your log is to grow. Note that LARGE transactions can
still cause growth, because they can't be truncated until fully
committed.
2. You are hurting your overall performance in one, possibly two,
ways. By repeatedly shrinking the log file, you are forcing SQL
Server to grow it again as needed, which introduces additional
overhead, possibly during a busy period. Also, repeatedly growing/
shrinking/growing/shrinking will lead to disk fragmentation, which
will also ultimately hurt your performance.
My advice would be to not use the log-shipping wizard that is built-
in. You can kill two birds with one stone by writing your own backup
routines. Create a log backup job that runs every hour (we do 5-
minute intervals here), have that job create backup files that contain
a date/time stamp, place these files into some folder, let's say
"FolderX". Write a log-shipping routine that monitors FolderX for new
files. When a new file is detected, your log shipping routine should
restore it and record the file name in a logging table. It then goes
back to monitoring FolderX for new files that aren't in the logging
table.
It's really not as complicated as it seems, and you'll solve all of
these problems...|||On 22 Feb, 21:06, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Feb 22, 2:48 pm, "Toby" <tjbeaum...@.gmail.com> wrote:
>
>
>
>
>
>
> Couple of key things here:
> 1. Log backups truncate the log - the more frequently that you run a
> log backup, the quicker committed transactions will get truncated, and
> the less likely your log is to grow. Note that LARGE transactions can
> still cause growth, because they can't be truncated until fully
> committed.
> 2. You are hurting your overall performance in one, possibly two,
> ways. By repeatedly shrinking the log file, you are forcing SQL
> Server to grow it again as needed, which introduces additional
> overhead, possibly during a busy period. Also, repeatedly growing/
> shrinking/growing/shrinking will lead to disk fragmentation, which
> will also ultimately hurt your performance.
> My advice would be to not use the log-shipping wizard that is built-
> in. You can kill two birds with one stone by writing your own backup
> routines. Create a log backup job that runs every hour (we do 5-
> minute intervals here), have that job create backup files that contain
> a date/time stamp, place these files into some folder, let's say
> "FolderX". Write a log-shipping routine that monitors FolderX for new
> files. When a new file is detected, your log shipping routine should
> restore it and record the file name in a logging table. It then goes
> back to monitoring FolderX for new files that aren't in the logging
> table.
> It's really not as complicated as it seems, and you'll solve all of
> these problems...
OK that's great thanks. I really don't know why I had the task shrink
the log in the first place, given the rate it increases.|||On 22 Feb, 21:06, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Feb 22, 2:48 pm, "Toby" <tjbeaum...@.gmail.com> wrote:
>
>
>
>
>
>
> Couple of key things here:
> 1. Log backups truncate the log - the more frequently that you run a
> log backup, the quicker committed transactions will get truncated, and
> the less likely your log is to grow. Note that LARGE transactions can
> still cause growth, because they can't be truncated until fully
> committed.
> 2. You are hurting your overall performance in one, possibly two,
> ways. By repeatedly shrinking the log file, you are forcing SQL
> Server to grow it again as needed, which introduces additional
> overhead, possibly during a busy period. Also, repeatedly growing/
> shrinking/growing/shrinking will lead to disk fragmentation, which
> will also ultimately hurt your performance.
> My advice would be to not use the log-shipping wizard that is built-
> in. You can kill two birds with one stone by writing your own backup
> routines. Create a log backup job that runs every hour (we do 5-
> minute intervals here), have that job create backup files that contain
> a date/time stamp, place these files into some folder, let's say
> "FolderX". Write a log-shipping routine that monitors FolderX for new
> files. When a new file is detected, your log shipping routine should
> restore it and record the file name in a logging table. It then goes
> back to monitoring FolderX for new files that aren't in the logging
> table.
> It's really not as complicated as it seems, and you'll solve all of
> these problems...
Hi again
I now have the transaction log shipping running fine - I have used the
Wizard for now so I'll see how it goes.
One thing though - having removed the additional process to backup and
truncate the log, the log is now not being truncated.
Any thoughts?
Thanks.|||On Feb 25, 6:44 am, "Toby" <tjbeaum...@.gmail.com> wrote:
> On 22 Feb, 21:06, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
> Hi again
> I now have the transaction log shipping running fine - I have used the
> Wizard for now so I'll see how it goes.
> One thing though - having removed the additional process to backup and
> truncate the log, the log is now not being truncated.
> Any thoughts?
> Thanks.
As I said, I would suggestion NOT using the wizards... I've never
used that log shipping wizard, I have no idea what sort of backup job
it creates. Create your OWN processes, then you know what's going
on...

Log shipping goes out of sync

Hi
I have two SQL 2000 boxes setup to log ship. The box being shipped to
is also the monitor
The size of the db being shipped is around 110GB - so the initial log
ship first creates and copies across the entire db (takes a while)
then begins the transaction logs.
The problem is that the log ship goes out of sync also immediately -
from testing I've managed to get the first shipped transaction log to
load, and sometimes the second, but never further as it gets out of
sync.
Things I've tried:
Changing the schedule from the default 15 minutes to 2 hours (for copy
and load)
Ensuring the log ship process doesn't clash with a routine backup
Any suggestions?
Thanks
TobyOn Feb 19, 7:40 am, "Toby" <tjbeaum...@.gmail.com> wrote:
> Hi
> I have two SQL 2000 boxes setup to log ship. The box being shipped to
> is also the monitor
> The size of the db being shipped is around 110GB - so the initial log
> ship first creates and copies across the entire db (takes a while)
> then begins the transaction logs.
> The problem is that the log ship goes out of sync also immediately -
> from testing I've managed to get the first shipped transaction log to
> load, and sometimes the second, but never further as it gets out of
> sync.
> Things I've tried:
> Changing the schedule from the default 15 minutes to 2 hours (for copy
> and load)
> Ensuring the log ship process doesn't clash with a routine backup
> Any suggestions?
> Thanks
> Toby
Please explain this further:
"Ensuring the log ship process doesn't clash with a routine backup"
Are you running transaction log backups IN ADDITION to the log
shipping process? This will break the log shipping chain. Log
shipping works by taking a backup of the transaction log and restoring
that backup onto another database. If you run your own independent
log backup, you're advancing the LSN pointer, throwing the log
shipping backups out of sync.|||Hi
In reply to your question "Are you running transaction log backups IN
ADDITION to the log
> shipping process? " - the answer is yes I am, so clearly here lies the problem.
I'm running a daily log backup, truncate and shrink - which I realise
now is going to cause issues with the log shipping. However, and
forgive me if this appears trivial, but the reason for run a log
backup, truncate and shrink is to prevent the log file getting too
big, as it currently grows at the rate of 20-30gb a day. If I rely on
the log shipping process only, will this provide adequate truncating
of the log?
Thanks|||On Feb 22, 2:48 pm, "Toby" <tjbeaum...@.gmail.com> wrote:
> Hi
> In reply to your question "Are you running transaction log backups IN
> ADDITION to the log
> > shipping process? " - the answer is yes I am, so clearly here lies the problem.
> I'm running a daily log backup, truncate and shrink - which I realise
> now is going to cause issues with the log shipping. However, and
> forgive me if this appears trivial, but the reason for run a log
> backup, truncate and shrink is to prevent the log file getting too
> big, as it currently grows at the rate of 20-30gb a day. If I rely on
> the log shipping process only, will this provide adequate truncating
> of the log?
> Thanks
Couple of key things here:
1. Log backups truncate the log - the more frequently that you run a
log backup, the quicker committed transactions will get truncated, and
the less likely your log is to grow. Note that LARGE transactions can
still cause growth, because they can't be truncated until fully
committed.
2. You are hurting your overall performance in one, possibly two,
ways. By repeatedly shrinking the log file, you are forcing SQL
Server to grow it again as needed, which introduces additional
overhead, possibly during a busy period. Also, repeatedly growing/
shrinking/growing/shrinking will lead to disk fragmentation, which
will also ultimately hurt your performance.
My advice would be to not use the log-shipping wizard that is built-
in. You can kill two birds with one stone by writing your own backup
routines. Create a log backup job that runs every hour (we do 5-
minute intervals here), have that job create backup files that contain
a date/time stamp, place these files into some folder, let's say
"FolderX". Write a log-shipping routine that monitors FolderX for new
files. When a new file is detected, your log shipping routine should
restore it and record the file name in a logging table. It then goes
back to monitoring FolderX for new files that aren't in the logging
table.
It's really not as complicated as it seems, and you'll solve all of
these problems...|||On 22 Feb, 21:06, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Feb 22, 2:48 pm, "Toby" <tjbeaum...@.gmail.com> wrote:
>
> > Hi
> > In reply to your question "Are you running transaction log backups IN
> > ADDITION to the log
> > > shipping process? " - the answer is yes I am, so clearly here lies the problem.
> > I'm running a daily log backup, truncate and shrink - which I realise
> > now is going to cause issues with the log shipping. However, and
> > forgive me if this appears trivial, but the reason for run a log
> > backup, truncate and shrink is to prevent the log file getting too
> > big, as it currently grows at the rate of 20-30gb a day. If I rely on
> > the log shipping process only, will this provide adequate truncating
> > of the log?
> > Thanks
> Couple of key things here:
> 1. Log backups truncate the log - the more frequently that you run a
> log backup, the quicker committed transactions will get truncated, and
> the less likely your log is to grow. Note that LARGE transactions can
> still cause growth, because they can't be truncated until fully
> committed.
> 2. You are hurting your overall performance in one, possibly two,
> ways. By repeatedly shrinking the log file, you are forcing SQL
> Server to grow it again as needed, which introduces additional
> overhead, possibly during a busy period. Also, repeatedly growing/
> shrinking/growing/shrinking will lead to disk fragmentation, which
> will also ultimately hurt your performance.
> My advice would be to not use the log-shipping wizard that is built-
> in. You can kill two birds with one stone by writing your own backup
> routines. Create a log backup job that runs every hour (we do 5-
> minute intervals here), have that job create backup files that contain
> a date/time stamp, place these files into some folder, let's say
> "FolderX". Write a log-shipping routine that monitors FolderX for new
> files. When a new file is detected, your log shipping routine should
> restore it and record the file name in a logging table. It then goes
> back to monitoring FolderX for new files that aren't in the logging
> table.
> It's really not as complicated as it seems, and you'll solve all of
> these problems...
OK that's great thanks. I really don't know why I had the task shrink
the log in the first place, given the rate it increases.|||On 22 Feb, 21:06, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Feb 22, 2:48 pm, "Toby" <tjbeaum...@.gmail.com> wrote:
>
> > Hi
> > In reply to your question "Are you running transaction log backups IN
> > ADDITION to the log
> > > shipping process? " - the answer is yes I am, so clearly here lies the problem.
> > I'm running a daily log backup, truncate and shrink - which I realise
> > now is going to cause issues with the log shipping. However, and
> > forgive me if this appears trivial, but the reason for run a log
> > backup, truncate and shrink is to prevent the log file getting too
> > big, as it currently grows at the rate of 20-30gb a day. If I rely on
> > the log shipping process only, will this provide adequate truncating
> > of the log?
> > Thanks
> Couple of key things here:
> 1. Log backups truncate the log - the more frequently that you run a
> log backup, the quicker committed transactions will get truncated, and
> the less likely your log is to grow. Note that LARGE transactions can
> still cause growth, because they can't be truncated until fully
> committed.
> 2. You are hurting your overall performance in one, possibly two,
> ways. By repeatedly shrinking the log file, you are forcing SQL
> Server to grow it again as needed, which introduces additional
> overhead, possibly during a busy period. Also, repeatedly growing/
> shrinking/growing/shrinking will lead to disk fragmentation, which
> will also ultimately hurt your performance.
> My advice would be to not use the log-shipping wizard that is built-
> in. You can kill two birds with one stone by writing your own backup
> routines. Create a log backup job that runs every hour (we do 5-
> minute intervals here), have that job create backup files that contain
> a date/time stamp, place these files into some folder, let's say
> "FolderX". Write a log-shipping routine that monitors FolderX for new
> files. When a new file is detected, your log shipping routine should
> restore it and record the file name in a logging table. It then goes
> back to monitoring FolderX for new files that aren't in the logging
> table.
> It's really not as complicated as it seems, and you'll solve all of
> these problems...
Hi again
I now have the transaction log shipping running fine - I have used the
Wizard for now so I'll see how it goes.
One thing though - having removed the additional process to backup and
truncate the log, the log is now not being truncated.
Any thoughts?
Thanks.|||On Feb 25, 6:44 am, "Toby" <tjbeaum...@.gmail.com> wrote:
> On 22 Feb, 21:06, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
>
> > On Feb 22, 2:48 pm, "Toby" <tjbeaum...@.gmail.com> wrote:
> > > Hi
> > > In reply to your question "Are you running transaction log backups IN
> > > ADDITION to the log
> > > > shipping process? " - the answer is yes I am, so clearly here lies the problem.
> > > I'm running a daily log backup, truncate and shrink - which I realise
> > > now is going to cause issues with the log shipping. However, and
> > > forgive me if this appears trivial, but the reason for run a log
> > > backup, truncate and shrink is to prevent the log file getting too
> > > big, as it currently grows at the rate of 20-30gb a day. If I rely on
> > > the log shipping process only, will this provide adequate truncating
> > > of the log?
> > > Thanks
> > Couple of key things here:
> > 1. Log backups truncate the log - the more frequently that you run a
> > log backup, the quicker committed transactions will get truncated, and
> > the less likely your log is to grow. Note that LARGE transactions can
> > still cause growth, because they can't be truncated until fully
> > committed.
> > 2. You are hurting your overall performance in one, possibly two,
> > ways. By repeatedly shrinking the log file, you are forcing SQL
> > Server to grow it again as needed, which introduces additional
> > overhead, possibly during a busy period. Also, repeatedly growing/
> > shrinking/growing/shrinking will lead to disk fragmentation, which
> > will also ultimately hurt your performance.
> > My advice would be to not use the log-shipping wizard that is built-
> > in. You can kill two birds with one stone by writing your own backup
> > routines. Create a log backup job that runs every hour (we do 5-
> > minute intervals here), have that job create backup files that contain
> > a date/time stamp, place these files into some folder, let's say
> > "FolderX". Write a log-shipping routine that monitors FolderX for new
> > files. When a new file is detected, your log shipping routine should
> > restore it and record the file name in a logging table. It then goes
> > back to monitoring FolderX for new files that aren't in the logging
> > table.
> > It's really not as complicated as it seems, and you'll solve all of
> > these problems...
> Hi again
> I now have the transaction log shipping running fine - I have used the
> Wizard for now so I'll see how it goes.
> One thing though - having removed the additional process to backup and
> truncate the log, the log is now not being truncated.
> Any thoughts?
> Thanks.
As I said, I would suggestion NOT using the wizards... I've never
used that log shipping wizard, I have no idea what sort of backup job
it creates. Create your OWN processes, then you know what's going
on...

Friday, February 24, 2012

Log shipping alerts

the "Log Shipping alert job-restore" job keeps showing a status of "failed" ,
however the databases are in sync. and yes I do have sp3;
mrdj - are there any entries in the system error log files that may point to
something?
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:E3C18911-0003-48AE-8E93-9D97A4B3C25C@.microsoft.com...
> the "Log Shipping alert job-restore" job keeps showing a status of
"failed" ,
> however the databases are in sync. and yes I do have sp3;
|||The only message is it states that the database is out of sync by XXX
minutes, which again does not make sense because as I run queries the
databases are in sync.
"JD" wrote:

> mrdj - are there any entries in the system error log files that may point to
> something?
>
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:E3C18911-0003-48AE-8E93-9D97A4B3C25C@.microsoft.com...
> "failed" ,
>
>
|||another interesting thing is that when i look in the Log Shipping Monitor
properties, although it shows the last backup file to be accurate, but for
the last file copied (and the last file loaded) it shows
"first_file_0000000000.trn" and the copy/load deltas show the same number of
minutes that the alert job claims to be out of sync.
"JD" wrote:

> mrdj - are there any entries in the system error log files that may point to
> something?
>
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:E3C18911-0003-48AE-8E93-9D97A4B3C25C@.microsoft.com...
> "failed" ,
>
>

Log shipping alerts

the "Log Shipping alert job-restore" job keeps showing a status of "failed"
,
however the databases are in sync. and yes I do have sp3;mrdj - are there any entries in the system error log files that may point to
something?
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:E3C18911-0003-48AE-8E93-9D97A4B3C25C@.microsoft.com...
> the "Log Shipping alert job-restore" job keeps showing a status of
"failed" ,
> however the databases are in sync. and yes I do have sp3;|||The only message is it states that the database is out of sync by XXX
minutes, which again does not make sense because as I run queries the
databases are in sync.
"JD" wrote:

> mrdj - are there any entries in the system error log files that may point
to
> something?
>
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:E3C18911-0003-48AE-8E93-9D97A4B3C25C@.microsoft.com...
> "failed" ,
>
>|||another interesting thing is that when i look in the Log Shipping Monitor
properties, although it shows the last backup file to be accurate, but for
the last file copied (and the last file loaded) it shows
"first_file_0000000000.trn" and the copy/load deltas show the same number of
minutes that the alert job claims to be out of sync.
"JD" wrote:

> mrdj - are there any entries in the system error log files that may point
to
> something?
>
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:E3C18911-0003-48AE-8E93-9D97A4B3C25C@.microsoft.com...
> "failed" ,
>
>

Log shipping alerts

the "Log Shipping alert job-restore" job keeps showing a status of "failed" ,
however the databases are in sync. and yes I do have sp3;mrdj - are there any entries in the system error log files that may point to
something?
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:E3C18911-0003-48AE-8E93-9D97A4B3C25C@.microsoft.com...
> the "Log Shipping alert job-restore" job keeps showing a status of
"failed" ,
> however the databases are in sync. and yes I do have sp3;|||The only message is it states that the database is out of sync by XXX
minutes, which again does not make sense because as I run queries the
databases are in sync.
"JD" wrote:
> mrdj - are there any entries in the system error log files that may point to
> something?
>
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:E3C18911-0003-48AE-8E93-9D97A4B3C25C@.microsoft.com...
> > the "Log Shipping alert job-restore" job keeps showing a status of
> "failed" ,
> > however the databases are in sync. and yes I do have sp3;
>
>|||another interesting thing is that when i look in the Log Shipping Monitor
properties, although it shows the last backup file to be accurate, but for
the last file copied (and the last file loaded) it shows
"first_file_0000000000.trn" and the copy/load deltas show the same number of
minutes that the alert job claims to be out of sync.
"JD" wrote:
> mrdj - are there any entries in the system error log files that may point to
> something?
>
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:E3C18911-0003-48AE-8E93-9D97A4B3C25C@.microsoft.com...
> > the "Log Shipping alert job-restore" job keeps showing a status of
> "failed" ,
> > however the databases are in sync. and yes I do have sp3;
>
>

Friday, March 30, 2012

Log shipping using Enterpirse Ed. Monitor DB getting out of sync

Wednesday, March 28, 2012

Log shipping sync error 14421

Log shipping sync error 14421

Log shipping sync error 14421

Wednesday, March 21, 2012

Log Shipping out of sync

log shipping out of sync

Log Shipping out of sync

Log Shipping out of sync

log shipping not working

Monday, March 19, 2012

Log shipping monitor our of sync SQL Server 2005

Log shipping monitor our of sync SQL Server 2005

Log shipping monitor our of sync SQL Server 2005

log shipping login sync

Monday, March 12, 2012

Log Shipping going out-of-sync

Log shipping goes out of sync

Log shipping goes out of sync

Log shipping goes out of sync

Friday, February 24, 2012

Log shipping alerts

Log shipping alerts

Log shipping alerts

Log shipping alerts

Blog Archive

About Me