Log shipping Transactions Log backup on secondary server

Guys,
Right after I setup up log shipping, there are a few jobs created under
job on the secondary server called "Log Shipping Alert Job - Backup"
and "Transaction Log Backup Job for DB Maintenance Plan...".
Without doing anything I would get a red "X", meaning failed on the
"Log Shipping Alert Job -Backup". When I run execute the "Transaction
Log Backup Job for DB Maintenance Plan...", it would fail too. This
is under the secondary server.
Is this normal? If not, how do i fix this? Do people actually backup
the transaction log on the secondary server on a log shipping?
Thanks,
Tonyhttp://www.sql-server-performance.c...og_shipping.asp
<tractng@.gmail.com> wrote in message
news:1147763695.248207.126980@.j33g2000cwa.googlegroups.com...
> Guys,
> Right after I setup up log shipping, there are a few jobs created under
> job on the secondary server called "Log Shipping Alert Job - Backup"
> and "Transaction Log Backup Job for DB Maintenance Plan...".
>
> Without doing anything I would get a red "X", meaning failed on the
> "Log Shipping Alert Job -Backup". When I run execute the "Transaction
> Log Backup Job for DB Maintenance Plan...", it would fail too. This
> is under the secondary server.
> Is this normal? If not, how do i fix this? Do people actually backup
> the transaction log on the secondary server on a log shipping?
>
> Thanks,
> Tony
>|||You cannot do any type of backup of a database restored using either NORECOV
ERY or STANDBY. But you
should be prepared to do backups of that database the minute this becomes th
e production database
(when a fail over is performed).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tractng@.gmail.com> wrote in message news:1147763695.248207.126980@.j33g2000cwa.googlegroups.
com...
> Guys,
> Right after I setup up log shipping, there are a few jobs created under
> job on the secondary server called "Log Shipping Alert Job - Backup"
> and "Transaction Log Backup Job for DB Maintenance Plan...".
>
> Without doing anything I would get a red "X", meaning failed on the
> "Log Shipping Alert Job -Backup". When I run execute the "Transaction
> Log Backup Job for DB Maintenance Plan...", it would fail too. This
> is under the secondary server.
> Is this normal? If not, how do i fix this? Do people actually backup
> the transaction log on the secondary server on a log shipping?
>
> Thanks,
> Tony
>|||Tibor,
Just to clarify. You are saying I won't be able to do a backup when it
is in the standby state. When the fail over is performed, I should be
able to?
Thanks a lot,
Tony|||Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tractng@.gmail.com> wrote in message news:1147979332.349123.49520@.j55g2000cwa.googlegroups.c
om...
> Tibor,
> Just to clarify. You are saying I won't be able to do a backup when it
> is in the standby state. When the fail over is performed, I should be
> able to?
>
> Thanks a lot,
> Tony
>

Log shipping Transactions Log backup on secondary server

Guys,
Right after I setup up log shipping, there are a few jobs created under
job on the secondary server called "Log Shipping Alert Job - Backup"
and "Transaction Log Backup Job for DB Maintenance Plan...".
Without doing anything I would get a red "X", meaning failed on the
"Log Shipping Alert Job -Backup". When I run execute the "Transaction
Log Backup Job for DB Maintenance Plan...", it would fail too. This
is under the secondary server.
Is this normal? If not, how do i fix this? Do people actually backup
the transaction log on the secondary server on a log shipping?
Thanks,
TonyYou cannot do any type of backup of a database restored using either NORECOVERY or STANDBY. But you
should be prepared to do backups of that database the minute this becomes the production database
(when a fail over is performed).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tractng@.gmail.com> wrote in message news:1147763695.248207.126980@.j33g2000cwa.googlegroups.com...
> Guys,
> Right after I setup up log shipping, there are a few jobs created under
> job on the secondary server called "Log Shipping Alert Job - Backup"
> and "Transaction Log Backup Job for DB Maintenance Plan...".
>
> Without doing anything I would get a red "X", meaning failed on the
> "Log Shipping Alert Job -Backup". When I run execute the "Transaction
> Log Backup Job for DB Maintenance Plan...", it would fail too. This
> is under the secondary server.
> Is this normal? If not, how do i fix this? Do people actually backup
> the transaction log on the secondary server on a log shipping?
>
> Thanks,
> Tony
>|||http://www.sql-server-performance.com/sql_server_log_shipping.asp
<tractng@.gmail.com> wrote in message
news:1147763695.248207.126980@.j33g2000cwa.googlegroups.com...
> Guys,
> Right after I setup up log shipping, there are a few jobs created under
> job on the secondary server called "Log Shipping Alert Job - Backup"
> and "Transaction Log Backup Job for DB Maintenance Plan...".
>
> Without doing anything I would get a red "X", meaning failed on the
> "Log Shipping Alert Job -Backup". When I run execute the "Transaction
> Log Backup Job for DB Maintenance Plan...", it would fail too. This
> is under the secondary server.
> Is this normal? If not, how do i fix this? Do people actually backup
> the transaction log on the secondary server on a log shipping?
>
> Thanks,
> Tony
>|||Tibor,
Just to clarify. You are saying I won't be able to do a backup when it
is in the standby state. When the fail over is performed, I should be
able to?
Thanks a lot,
Tony|||Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tractng@.gmail.com> wrote in message news:1147979332.349123.49520@.j55g2000cwa.googlegroups.com...
> Tibor,
> Just to clarify. You are saying I won't be able to do a backup when it
> is in the standby state. When the fail over is performed, I should be
> able to?
>
> Thanks a lot,
> Tony
>

Log Shipping restore failing

I have a log shipping configuration set up that has been working for several weeks now. Last night starting at about 12:30 am the restore job has started failing periodically. It has gotten worse as the day has gone on, and the job history shows the same
error message each time it fails.
Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Can anyone tell me what this means? I am aware of the problem of nameing a directory with a -S in the name, and this is not the case here.
TIA,
Ken
Right-click on the maintenance plan name (not the SQL Agent Job) and select
Maintenance Plan History... for a more detailed error message.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:E8A88DA1-CAAA-45DB-A063-A4D5315D20F0@.microsoft.com...
> I have a log shipping configuration set up that has been working for
several weeks now. Last night starting at about 12:30 am the restore job has
started failing periodically. It has gotten worse as the day has gone on,
and the job history shows the same error message each time it fails.
> Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.
> Can anyone tell me what this means? I am aware of the problem of nameing
a directory with a -S in the name, and this is not the case here.
> TIA,
> Ken
|||I have since found out that running indexing operations on the database while the log shipping file is being created can cause this problem, and that's exactly what happened. My next question is how do I get past a corrupted transaction log file without s
crewing up the whole chain of transaction logs? The rest are stacking up behind it every 15 minutes because this one log file won't restore.
Thanks,
Ken
|||Since the log sequence is broken, you will likely have to re-initialize log
shipping with a full backup-restore.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:DC38D04C-0745-400C-8F83-290F3EF46369@.microsoft.com...
> I have since found out that running indexing operations on the database
while the log shipping file is being created can cause this problem, and
that's exactly what happened. My next question is how do I get past a
corrupted transaction log file without screwing up the whole chain of
transaction logs? The rest are stacking up behind it every 15 minutes
because this one log file won't restore.
> Thanks,
> Ken

Log Shipping restore failing

I have a log shipping configuration set up that has been working for several weeks now. Last night starting at about 12:30 am the restore job has started failing periodically. It has gotten worse as the day has gone on, and the job history shows the same error message each time it fails
Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed
Can anyone tell me what this means? I am aware of the problem of nameing a directory with a -S in the name, and this is not the case here
TIA
KenRight-click on the maintenance plan name (not the SQL Agent Job) and select
Maintenance Plan History... for a more detailed error message.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:E8A88DA1-CAAA-45DB-A063-A4D5315D20F0@.microsoft.com...
> I have a log shipping configuration set up that has been working for
several weeks now. Last night starting at about 12:30 am the restore job has
started failing periodically. It has gotten worse as the day has gone on,
and the job history shows the same error message each time it fails.
> Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.
> Can anyone tell me what this means? I am aware of the problem of nameing
a directory with a -S in the name, and this is not the case here.
> TIA,
> Ken|||Since the log sequence is broken, you will likely have to re-initialize log
shipping with a full backup-restore.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:DC38D04C-0745-400C-8F83-290F3EF46369@.microsoft.com...
> I have since found out that running indexing operations on the database
while the log shipping file is being created can cause this problem, and
that's exactly what happened. My next question is how do I get past a
corrupted transaction log file without screwing up the whole chain of
transaction logs? The rest are stacking up behind it every 15 minutes
because this one log file won't restore.
> Thanks,
> Ken

Log Shipping restore failing

I have a log shipping configuration set up that has been working for several
weeks now. Last night starting at about 12:30 am the restore job has starte
d failing periodically. It has gotten worse as the day has gone on, and the
job history shows the same
error message each time it fails.
Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE 42
000] (Error 22029). The step failed.
Can anyone tell me what this means? I am aware of the problem of nameing a
directory with a -S in the name, and this is not the case here.
TIA,
KenRight-click on the maintenance plan name (not the SQL Agent Job) and select
Maintenance Plan History... for a more detailed error message.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:E8A88DA1-CAAA-45DB-A063-A4D5315D20F0@.microsoft.com...
> I have a log shipping configuration set up that has been working for
several weeks now. Last night starting at about 12:30 am the restore job has
started failing periodically. It has gotten worse as the day has gone on,
and the job history shows the same error message each time it fails.
> Executed as user: PROMAXONLINE\svcsql. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.
> Can anyone tell me what this means? I am aware of the problem of nameing
a directory with a -S in the name, and this is not the case here.
> TIA,
> Ken|||I have since found out that running indexing operations on the database whil
e the log shipping file is being created can cause this problem, and that's
exactly what happened. My next question is how do I get past a corrupted tra
nsaction log file without s
crewing up the whole chain of transaction logs? The rest are stacking up beh
ind it every 15 minutes because this one log file won't restore.
Thanks,
Ken|||Since the log sequence is broken, you will likely have to re-initialize log
shipping with a full backup-restore.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ken Krause" <anonymous@.discussions.microsoft.com> wrote in message
news:DC38D04C-0745-400C-8F83-290F3EF46369@.microsoft.com...
> I have since found out that running indexing operations on the database
while the log shipping file is being created can cause this problem, and
that's exactly what happened. My next question is how do I get past a
corrupted transaction log file without screwing up the whole chain of
transaction logs? The rest are stacking up behind it every 15 minutes
because this one log file won't restore.
> Thanks,
> Kensql

Log shipping on Secondary cannot load large log file

We have log shipping from a primary to a secondary for warm standby and
reporting.
After a reindexing job that caused a particularly large log backup to be
created (9,141,927,424 bytes), the secondary appears to have pre-allocated
the disk space required to copy the file over (the file has a time stamp for
the time at which the Log-ship copy was scheduled) but after 5+ hours the
time stamp on the file has not changed and the log-ship copy/restore history
reports errors like:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_tlog_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
When I inspect the process in the secondary that appears to be doing the
copy I see the following:
EXECUTE master.dbo.xp_sqlmaint '-LSCopyPlanID
"87C86B58-847F-4505-AFAE-4A1F9E31211E"'
Status: runnable
CPU: 0
Physical I/O: 0
Memory Usage: 2
Login time: 31/8/2004 1:00:07 PM
Last Batch: 31/8/2004 1:00:07 PM
As you can see the process seems to have stalled.
As far as disk and network through-put is concerned I would expect a worst
case scenario of 10 Mbytes per second - more than enough to copy the file
within the time since it was created.
Is there a way to recover from this situation without having to remove log
shipping and starting from scratch?
Any tips appreciated.Hello David
I'm not clear on the problem description that you have outlined in your
message. As I understand it, you are mentioning the following sequence of
events (please correct me if I'm wrong) :
1. You had a large tran log backup that has the same timestamp in the
filename as when the copy job ran.
2. You noticed, 5 hours after the copy job ran, that the restore job failed
with the error :
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_tlog_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
I wanted to point out couple of things here :
1. The filename for the transaction log backup does not change through the
entire log shipping life cycle.
2. The filename of the transaction log backup file and the time the copy
job runs has no relation
3. Copy and Restore jobs on the secondary server are 2 different jobs and
run independent of each other.
4. The failure that you see above is reported by the Restore job (hence the
message "RESTORE LOG is terminating abnormally")
The question that I wanted to ask you was - Are you absolutely positive
that the copy job has completed copying the file to the secondary server?
Please confirm this by either trying to rename the file (and then renaming
it back to its original name) or using the HANDLE utility from
SYSINTERNALS.COM
As you have mentioned later in your message, it seems like the copy job is
still running. I would advise you to wait for the job to complete.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Pankaj,
Thanks for the reply.
To cut a long story short, I killed the copy process that appeared to have
stalled.
In due course a new process started and the file was restored into the
secondary within a reasonably short amount of time.
I realise that killing a job like that is risky and normally you should
leave it run, but in my case we have a 55 GB database that requries the
log-shipping secondary to be highly available and up-to-date for reporting.
My choices were:
1) Kill the job and hope the secondary came back into synch within a couple
of hours.
2) Remove log-shipping and wait 8+ hours for the secondary to rebuild from
scratch.
I took a gamble and chose option 1.
"Pankaj Agarwal [MSFT]" wrote:
> Hello David
> I'm not clear on the problem description that you have outlined in your
> message. As I understand it, you are mentioning the following sequence of
> events (please correct me if I'm wrong) :
> 1. You had a large tran log backup that has the same timestamp in the
> filename as when the copy job ran.
> 2. You noticed, 5 hours after the copy job ran, that the restore job failed
> with the error :
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Cannot open backup device
> 'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_tlog_200408311200.TRN'.
> Device error or device off-line. See the SQL Server error log for more
> details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> abnormally.
> I wanted to point out couple of things here :
> 1. The filename for the transaction log backup does not change through the
> entire log shipping life cycle.
> 2. The filename of the transaction log backup file and the time the copy
> job runs has no relation
> 3. Copy and Restore jobs on the secondary server are 2 different jobs and
> run independent of each other.
> 4. The failure that you see above is reported by the Restore job (hence the
> message "RESTORE LOG is terminating abnormally")
> The question that I wanted to ask you was - Are you absolutely positive
> that the copy job has completed copying the file to the secondary server?
> Please confirm this by either trying to rename the file (and then renaming
> it back to its original name) or using the HANDLE utility from
> SYSINTERNALS.COM
> As you have mentioned later in your message, it seems like the copy job is
> still running. I would advise you to wait for the job to complete.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>

Log shipping on Secondary cannot load large log file

We have log shipping from a primary to a secondary for warm standby and
reporting.
After a reindexing job that caused a particularly large log backup to be
created (9,141,927,424 bytes), the secondary appears to have pre-allocated
the disk space required to copy the file over (the file has a time stamp for
the time at which the Log-ship copy was scheduled) but after 5+ hours the
time stamp on the file has not changed and the log-ship copy/restore history
reports errors like:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_t log_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
When I inspect the process in the secondary that appears to be doing the
copy I see the following:
EXECUTE master.dbo.xp_sqlmaint '-LSCopyPlanID
"87C86B58-847F-4505-AFAE-4A1F9E31211E"'
Status: runnable
CPU: 0
Physical I/O: 0
Memory Usage: 2
Login time: 31/8/2004 1:00:07 PM
Last Batch: 31/8/2004 1:00:07 PM
As you can see the process seems to have stalled.
As far as disk and network through-put is concerned I would expect a worst
case scenario of 10 Mbytes per second - more than enough to copy the file
within the time since it was created.
Is there a way to recover from this situation without having to remove log
shipping and starting from scratch?
Any tips appreciated.
Hello David
I'm not clear on the problem description that you have outlined in your
message. As I understand it, you are mentioning the following sequence of
events (please correct me if I'm wrong) :
1. You had a large tran log backup that has the same timestamp in the
filename as when the copy job ran.
2. You noticed, 5 hours after the copy job ran, that the restore job failed
with the error :
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_t log_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
I wanted to point out couple of things here :
1. The filename for the transaction log backup does not change through the
entire log shipping life cycle.
2. The filename of the transaction log backup file and the time the copy
job runs has no relation
3. Copy and Restore jobs on the secondary server are 2 different jobs and
run independent of each other.
4. The failure that you see above is reported by the Restore job (hence the
message "RESTORE LOG is terminating abnormally")
The question that I wanted to ask you was - Are you absolutely positive
that the copy job has completed copying the file to the secondary server?
Please confirm this by either trying to rename the file (and then renaming
it back to its original name) or using the HANDLE utility from
SYSINTERNALS.COM
As you have mentioned later in your message, it seems like the copy job is
still running. I would advise you to wait for the job to complete.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Hi Pankaj,
Thanks for the reply.
To cut a long story short, I killed the copy process that appeared to have
stalled.
In due course a new process started and the file was restored into the
secondary within a reasonably short amount of time.
I realise that killing a job like that is risky and normally you should
leave it run, but in my case we have a 55 GB database that requries the
log-shipping secondary to be highly available and up-to-date for reporting.
My choices were:
1) Kill the job and hope the secondary came back into synch within a couple
of hours.
2) Remove log-shipping and wait 8+ hours for the secondary to rebuild from
scratch.
I took a gamble and chose option 1.
"Pankaj Agarwal [MSFT]" wrote:

> Hello David
> I'm not clear on the problem description that you have outlined in your
> message. As I understand it, you are mentioning the following sequence of
> events (please correct me if I'm wrong) :
> 1. You had a large tran log backup that has the same timestamp in the
> filename as when the copy job ran.
> 2. You noticed, 5 hours after the copy job ran, that the restore job failed
> with the error :
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Cannot open backup device
> 'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\OASIS_TTC_t log_200408311200.TRN'.
> Device error or device off-line. See the SQL Server error log for more
> details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> abnormally.
> I wanted to point out couple of things here :
> 1. The filename for the transaction log backup does not change through the
> entire log shipping life cycle.
> 2. The filename of the transaction log backup file and the time the copy
> job runs has no relation
> 3. Copy and Restore jobs on the secondary server are 2 different jobs and
> run independent of each other.
> 4. The failure that you see above is reported by the Restore job (hence the
> message "RESTORE LOG is terminating abnormally")
> The question that I wanted to ask you was - Are you absolutely positive
> that the copy job has completed copying the file to the secondary server?
> Please confirm this by either trying to rename the file (and then renaming
> it back to its original name) or using the HANDLE utility from
> SYSINTERNALS.COM
> As you have mentioned later in your message, it seems like the copy job is
> still running. I would advise you to wait for the job to complete.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>

Log shipping on Secondary cannot load large log file

We have log shipping from a primary to a secondary for warm standby and
reporting.
After a reindexing job that caused a particularly large log backup to be
created (9,141,927,424 bytes), the secondary appears to have pre-allocated
the disk space required to copy the file over (the file has a time stamp for
the time at which the Log-ship copy was scheduled) but after 5+ hours the
time stamp on the file has not changed and the log-ship copy/restore history
reports errors like:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\O
ASIS_TTC_tlog_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is te
rminating
abnormally.
When I inspect the process in the secondary that appears to be doing the
copy I see the following:
EXECUTE master.dbo.xp_sqlmaint '-LSCopyPlanID
"87C86B58-847F-4505-AFAE-4A1F9E31211E"'
Status: runnable
CPU: 0
Physical I/O: 0
Memory Usage: 2
Login time: 31/8/2004 1:00:07 PM
Last Batch: 31/8/2004 1:00:07 PM
As you can see the process seems to have stalled.
As far as disk and network through-put is concerned I would expect a worst
case scenario of 10 Mbytes per second - more than enough to copy the file
within the time since it was created.
Is there a way to recover from this situation without having to remove log
shipping and starting from scratch?
Any tips appreciated.Hello David
I'm not clear on the problem description that you have outlined in your
message. As I understand it, you are mentioning the following sequence of
events (please correct me if I'm wrong) :
1. You had a large tran log backup that has the same timestamp in the
filename as when the copy job ran.
2. You noticed, 5 hours after the copy job ran, that the restore job failed
with the error :
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\O
ASIS_TTC_tlog_200408311200.TRN'.
Device error or device off-line. See the SQL Server error log for more
details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is te
rminating
abnormally.
I wanted to point out couple of things here :
1. The filename for the transaction log backup does not change through the
entire log shipping life cycle.
2. The filename of the transaction log backup file and the time the copy
job runs has no relation
3. Copy and Restore jobs on the secondary server are 2 different jobs and
run independent of each other.
4. The failure that you see above is reported by the Restore job (hence the
message "RESTORE LOG is terminating abnormally")
The question that I wanted to ask you was - Are you absolutely positive
that the copy job has completed copying the file to the secondary server?
Please confirm this by either trying to rename the file (and then renaming
it back to its original name) or using the HANDLE utility from
SYSINTERNALS.COM
As you have mentioned later in your message, it seems like the copy job is
still running. I would advise you to wait for the job to complete.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Pankaj,
Thanks for the reply.
To cut a long story short, I killed the copy process that appeared to have
stalled.
In due course a new process started and the file was restored into the
secondary within a reasonably short amount of time.
I realise that killing a job like that is risky and normally you should
leave it run, but in my case we have a 55 GB database that requries the
log-shipping secondary to be highly available and up-to-date for reporting.
My choices were:
1) Kill the job and hope the secondary came back into synch within a couple
of hours.
2) Remove log-shipping and wait 8+ hours for the secondary to rebuild from
scratch.
I took a gamble and chose option 1.
"Pankaj Agarwal [MSFT]" wrote:

> Hello David
> I'm not clear on the problem description that you have outlined in your
> message. As I understand it, you are mentioning the following sequence of
> events (please correct me if I'm wrong) :
> 1. You had a large tran log backup that has the same timestamp in the
> filename as when the copy job ran.
> 2. You noticed, 5 hours after the copy job ran, that the restore job faile
d
> with the error :
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]Cannot open backup device
> 'H:\SQLData\MSSQL$NODEB\BACKUP\LogShip\O
ASIS_TTC_tlog_200408311200.TRN'.
> Device error or device off-line. See the SQL Server error log for more
> details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is
terminating
> abnormally.
> I wanted to point out couple of things here :
> 1. The filename for the transaction log backup does not change through the
> entire log shipping life cycle.
> 2. The filename of the transaction log backup file and the time the copy
> job runs has no relation
> 3. Copy and Restore jobs on the secondary server are 2 different jobs and
> run independent of each other.
> 4. The failure that you see above is reported by the Restore job (hence th
e
> message "RESTORE LOG is terminating abnormally")
> The question that I wanted to ask you was - Are you absolutely positive
> that the copy job has completed copying the file to the secondary server?
> Please confirm this by either trying to rename the file (and then renaming
> it back to its original name) or using the HANDLE utility from
> SYSINTERNALS.COM
> As you have mentioned later in your message, it seems like the copy job is
> still running. I would advise you to wait for the job to complete.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>sql

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

Log Shipping Jobs please help us (Urgent need)

Hi,
When i am changing Log Shipping job owner in Monitor server, stand by server
and primary server, i am getting error as "Error 14269:Job 'Log shipping
alert job-backup' is already targetted at server. xxxxxx(SQL Instance) The
Job was not saved.
please advise how to chnage the Job owners, if we are getting these errors.
Thanks,
John
All, i am working with MS Support now for this error. we analyzed due to
sp_add_job and other related stored procedures does not take instance name
more than 30 chars (our servers have more than 30 chars).
so when ever we are setting up Log shipping or replication, shall we need
our SQL Intance should be lesthan 30 character? my friend is telling for
Oracle databases they dont have any problem with intance name length.
hope this scenario may help someone in future.
good lcuk to all, John
"John" wrote:

> Hi,
> When i am changing Log Shipping job owner in Monitor server, stand by server
> and primary server, i am getting error as "Error 14269:Job 'Log shipping
> alert job-backup' is already targetted at server. xxxxxx(SQL Instance) The
> Job was not saved.
> please advise how to chnage the Job owners, if we are getting these errors.
> Thanks,
> John
|||John,
You could create aliases with shorter names. Open the client network
utility and go to Aliases.
M

log shipping job

does anyone know about these tables on msdb: backup_movement_plans and
backup_movement_plan_database? Are they related to log shipping jobs? When we
create the log shipping jobs(copy and load) using script, how the data in
those tables get populated? and how the job know to retreive the data from
the tables? thanksI don't think these tables have anything to do with log shipping. Do you
have any third party applications running on or accessing the database?
Perhaps one such application might have added these tables for handling some
administrative tasks.
--
Anith|||Just found that these are tables from SQL Server 7.0 version which I hadn't
thought about before. Yes, these are valid log shipping related tables in
SQL Server 7.0. The log shipping scheme has changed considerably with SQL
2000 however.
Search for an article by Ron Talmage on log shipping with SQL 7.0 and you
might find some more details about these tables.
--
Anith|||we are currently on 2000 SE version, since has no EE, we have to create the
jobs to do log shipping (copy and load), so do those tables need to manually
created also? thanks
"Anith Sen" wrote:
> Just found that these are tables from SQL Server 7.0 version which I hadn't
> thought about before. Yes, these are valid log shipping related tables in
> SQL Server 7.0. The log shipping scheme has changed considerably with SQL
> 2000 however.
> Search for an article by Ron Talmage on log shipping with SQL 7.0 and you
> might find some more details about these tables.
> --
> Anith
>
>

log shipping job

does anyone know about these tables on msdb: backup_movement_plans and
backup_movement_plan_database? Are they related to log shipping jobs? When we
create the log shipping jobs(copy and load) using script, how the data in
those tables get populated? and how the job know to retreive the data from
the tables? thanks
I don't think these tables have anything to do with log shipping. Do you
have any third party applications running on or accessing the database?
Perhaps one such application might have added these tables for handling some
administrative tasks.
Anith
|||Just found that these are tables from SQL Server 7.0 version which I hadn't
thought about before. Yes, these are valid log shipping related tables in
SQL Server 7.0. The log shipping scheme has changed considerably with SQL
2000 however.
Search for an article by Ron Talmage on log shipping with SQL 7.0 and you
might find some more details about these tables.
Anith
|||we are currently on 2000 SE version, since has no EE, we have to create the
jobs to do log shipping (copy and load), so do those tables need to manually
created also? thanks
"Anith Sen" wrote:

> Just found that these are tables from SQL Server 7.0 version which I hadn't
> thought about before. Yes, these are valid log shipping related tables in
> SQL Server 7.0. The log shipping scheme has changed considerably with SQL
> 2000 however.
> Search for an article by Ron Talmage on log shipping with SQL 7.0 and you
> might find some more details about these tables.
> --
> Anith
>
>

Log Shipping Issue

I had log shipping set up from serverA to serverB. One day, I start getting the automated SQL Server job failure messages.

JOB RUN: 'Log Shipping Restore for ServerA.db_nm_logshipping' was run on 10/19/2007 at 10:15:46 AM
DURATION: 0 hours, 0 minutes, 0 seconds
STATUS: Failed
MESSAGES: The job failed. Unable to retrieve steps for job Log Shipping Restore for ServerA.db_nm_logshipping.

After a few troubleshooting steps, I eventually removed and remade the maintenance plan and all associated log shipping jobs (or so I thought). For some reason, even though the new log shipping job is working properly, I'm still getting error messages from an old job.

I've ran a number of queries trying to find rogue entries still in system tables that could possibly still be trying to execute this job:

select * from dbo.log_shipping_databases where database_name = 'db_nm'
select * from dbo.log_shipping_monitor
select * from dbo.log_shipping_plan_databases where source_database = 'db_nm'
select * from dbo.log_shipping_plan_history where source_database = 'db_nm'
select * from dbo.log_shipping_plans where plan_name like '%db_nm%'
select * from dbo.log_shipping_primaries where primary_database_name = 'db_nm'
select * from dbo.log_shipping_secondaries where secondary_database_name = 'db_nm'
select * from dbo.sysdbmaintplan_databases where database_name = 'db_nm'
select * from dbo.sysdbmaintplan_jobs
select * from dbo.sysdbmaintplans where plan_name like '%db_nm%'
select * from dbo.sysjobs where name like '%db_nm%'

I couldn't find anything!

Does anyone have any idea what could be transpiring here? Any assistance would be GREATLY appreciated.

Thanks in advance!

-JoeAs a follow-up:

I ended up restarting SQL Agent. That somehow cleared out the issue.

Log Shipping fails after DB Reindex.

Can anyone help.
I have set up log shipping between two SQL 2000 servers, with no issues. A
job runs on the Primary server to reindex the databases. At this point Log
Shipping Fails, the error massage says that the servers are out of sync.
The log file shows the error
The log in this backup set begins at LSN 202000000017000001, which is too
late to apply to the database. An earlier log backup that includes LSN
20200000
0016800001 can be restored.
Is there a way to reindex the database that does not affect log shipping?
Many thanks in advance
Andy McDonald
MCSA"Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in message
news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
> Can anyone help.
> I have set up log shipping between two SQL 2000 servers, with no issues. A
> job runs on the Primary server to reindex the databases. At this point Log
> Shipping Fails, the error massage says that the servers are out of sync.
> The log file shows the error
> The log in this backup set begins at LSN 202000000017000001, which is too
> late to apply to the database. An earlier log backup that includes LSN
> 20200000
> 0016800001 can be restored.
> Is there a way to reindex the database that does not affect log shipping?
It sounds more like somehow you're losing a log backup somewhere along the
line.
reindexing doesn't (or at least shouldn't) affect log-shipping (having just
done this lately).
> Many thanks in advance
> Andy McDonald
> MCSA|||Greg
The data base is a large one, (about 25 GB) dont know if this helps. but we
only receive the error when re-indexing takes place.
Andy McDonald
MCSA
"Greg D. Moore (Strider)" wrote:
> "Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in message
> news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
> > Can anyone help.
> >
> > I have set up log shipping between two SQL 2000 servers, with no issues. A
> > job runs on the Primary server to reindex the databases. At this point Log
> > Shipping Fails, the error massage says that the servers are out of sync.
> >
> > The log file shows the error
> > The log in this backup set begins at LSN 202000000017000001, which is too
> > late to apply to the database. An earlier log backup that includes LSN
> > 20200000
> > 0016800001 can be restored.
> >
> > Is there a way to reindex the database that does not affect log shipping?
> It sounds more like somehow you're losing a log backup somewhere along the
> line.
> reindexing doesn't (or at least shouldn't) affect log-shipping (having just
> done this lately).
>
> >
> > Many thanks in advance
> > Andy McDonald
> > MCSA
>
>|||Are you 100% certain that you don't set the database to simple recovery mode during reindex?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy McDonald" <AndyMcDonald@.discussions.microsoft.com> wrote in message
news:695642A8-9954-472D-9C90-ECC295BAB791@.microsoft.com...
> Greg
> The data base is a large one, (about 25 GB) dont know if this helps. but we
> only receive the error when re-indexing takes place.
> Andy McDonald
> MCSA
> "Greg D. Moore (Strider)" wrote:
>> "Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in message
>> news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
>> > Can anyone help.
>> >
>> > I have set up log shipping between two SQL 2000 servers, with no issues. A
>> > job runs on the Primary server to reindex the databases. At this point Log
>> > Shipping Fails, the error massage says that the servers are out of sync.
>> >
>> > The log file shows the error
>> > The log in this backup set begins at LSN 202000000017000001, which is too
>> > late to apply to the database. An earlier log backup that includes LSN
>> > 20200000
>> > 0016800001 can be restored.
>> >
>> > Is there a way to reindex the database that does not affect log shipping?
>> It sounds more like somehow you're losing a log backup somewhere along the
>> line.
>> reindexing doesn't (or at least shouldn't) affect log-shipping (having just
>> done this lately).
>>
>> >
>> > Many thanks in advance
>> > Andy McDonald
>> > MCSA
>>|||Hello
Yes I am sure.
"Tibor Karaszi" wrote:
> Are you 100% certain that you don't set the database to simple recovery mode during reindex?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andy McDonald" <AndyMcDonald@.discussions.microsoft.com> wrote in message
> news:695642A8-9954-472D-9C90-ECC295BAB791@.microsoft.com...
> > Greg
> >
> > The data base is a large one, (about 25 GB) dont know if this helps. but we
> > only receive the error when re-indexing takes place.
> >
> > Andy McDonald
> > MCSA
> >
> > "Greg D. Moore (Strider)" wrote:
> >
> >>
> >> "Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in message
> >> news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
> >> > Can anyone help.
> >> >
> >> > I have set up log shipping between two SQL 2000 servers, with no issues. A
> >> > job runs on the Primary server to reindex the databases. At this point Log
> >> > Shipping Fails, the error massage says that the servers are out of sync.
> >> >
> >> > The log file shows the error
> >> > The log in this backup set begins at LSN 202000000017000001, which is too
> >> > late to apply to the database. An earlier log backup that includes LSN
> >> > 20200000
> >> > 0016800001 can be restored.
> >> >
> >> > Is there a way to reindex the database that does not affect log shipping?
> >>
> >> It sounds more like somehow you're losing a log backup somewhere along the
> >> line.
> >>
> >> reindexing doesn't (or at least shouldn't) affect log-shipping (having just
> >> done this lately).
> >>
> >>
> >> >
> >> > Many thanks in advance
> >> > Andy McDonald
> >> > MCSA
> >>
> >>
> >>
>
>|||"Andy McDonald" <AndyMcDonald@.discussions.microsoft.com> wrote in message
news:695642A8-9954-472D-9C90-ECC295BAB791@.microsoft.com...
> Greg
> The data base is a large one, (about 25 GB) dont know if this helps. but
we
> only receive the error when re-indexing takes place.
Very weird. Really sounds like a log is benig lost somewhere along the
line.
Which I had a better answer.
> Andy McDonald
> MCSA
> "Greg D. Moore (Strider)" wrote:
> >
> > "Andy McDonald" <Andy McDonald@.discussions.microsoft.com> wrote in
message
> > news:7ED44ADD-C2FF-4E2F-B371-CC3180FC8B8A@.microsoft.com...
> > > Can anyone help.
> > >
> > > I have set up log shipping between two SQL 2000 servers, with no
issues. A
> > > job runs on the Primary server to reindex the databases. At this point
Log
> > > Shipping Fails, the error massage says that the servers are out of
sync.
> > >
> > > The log file shows the error
> > > The log in this backup set begins at LSN 202000000017000001, which is
too
> > > late to apply to the database. An earlier log backup that includes LSN
> > > 20200000
> > > 0016800001 can be restored.
> > >
> > > Is there a way to reindex the database that does not affect log
shipping?
> >
> > It sounds more like somehow you're losing a log backup somewhere along
the
> > line.
> >
> > reindexing doesn't (or at least shouldn't) affect log-shipping (having
just
> > done this lately).
> >
> >
> > >
> > > Many thanks in advance
> > > Andy McDonald
> > > MCSA
> >
> >
> >

log shipping error message

hi,
We set up a log shipping job on our control server for one
of our sql servers and its fail-over server. We keep
getting these kind of job failures from time to time:
'The job failed. The Job was invoked by Schedule 12 (Log
Shipping Alert Job - Restore). The last step to run was
step 1 (Log Shipping Alert Job - Restore).
Executed as user: domainName\ControlServerName. The log
shipping destination fail_overServerName.databaseName is
out of sync by 30 minutes. [SQLSTATE 42000] (Error 14421)
Associated statement is not prepared [SQLSTATE HY007]
(Error 0). The step failed.'
What does this 'Associated statement is not prepared' mean?
many thanks.
JJJJ,
You might experience these symptoms if you configured primary or secondary
servers (or both) to connect to the monitor server by using SQL Server
Authentication on a computer that is running SQL Server 2000 Service Pack 2
(SP2)
To resolve this problem, obtain the latest service pack for Microsoft SQL
Server 2000.
SQL Server 2000 Service Pack 3a (SP3a) is available from the following
Microsoft Web site: http://www.microsoft.com/sql/downloads/2000/sp3.asp
To work around this problem, click the Use Windows Authentication option in
the Specify the Log Shipping Monitor Server Information dialog box during
log shipping Setup.
Background info for Error 14421 follows:
By definition, message 14421 does not necessarily indicate a problem with
Log Shipping. This message indicates that the difference between the last
backed up file and last restored file is greater than the time selected for
the Out of Sync Alert threshold.
There are serveral reasons why the alert message is raised. The following
list includes some of these reasons:
1.. The date or time (or both) on the primary server is modified such that
the date or time on the primary server is significantly ahead between
consecutive transaction log backups.
2.. The log shipping Restore job that is running on the secondary server
cannot connect to the monitor server msdb database to update the
log_shipping_secondaries table with the correct value. This may be the
result of an authentication problem between the secondary server and the
monitor server.
3.. You may have set an incorrect value for the Out of Sync Alert
threshold. Ideally, you must set this value to at least three times the
frequency of the slower of the Copy and Restore jobs. If the frequency of
the Copy or Restore jobs is modified after log shipping is set up and
functional, you must modify the value of the Out of Sync Alert threshold
accordingly.
4.. Problems either with the Backup job or Copy job are most likely to
result in "out of sync" alert messages. If "out of sync" alert messages are
raised and if there are no problems with the Backup or the Restore job,
check the Copy job for potential problems. Additionally, network
connectivity may cause the Copy job to fail.
5.. It is also possible that the Restore job on the secondary server is
failing. In this case, check the job history for the Restore job because it
may indicate a reason for the failure.
--
Keith Wilson
This posting is provided "AS IS" without express or implied warranty,
guarantee, or rights.
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:05b001c3b2f4$44286c10$a501280a@.phx.gbl...
> hi,
> We set up a log shipping job on our control server for one
> of our sql servers and its fail-over server. We keep
> getting these kind of job failures from time to time:
> 'The job failed. The Job was invoked by Schedule 12 (Log
> Shipping Alert Job - Restore). The last step to run was
> step 1 (Log Shipping Alert Job - Restore).
> Executed as user: domainName\ControlServerName. The log
> shipping destination fail_overServerName.databaseName is
> out of sync by 30 minutes. [SQLSTATE 42000] (Error 14421)
> Associated statement is not prepared [SQLSTATE HY007]
> (Error 0). The step failed.'
> What does this 'Associated statement is not prepared' mean?
> many thanks.
> JJ
>

log shipping error

I have successfully configured log shipping, and verified that the standby
server is being updated correctly.
There is one job named "Log Shipping Alert Job - Backup" which fails every
minute.
In the job history, it says:
'The job shipping source SERVERNAME.DATABASE has not backed up for 190
minutes. [SQLSTATE 42000] [Error 14420] The step failed.'
I guess that this can be disregarded, but supervisors get nervous about
errors, and suspect that the log shipping is not reliable.
How can I make this job succeed?
Thanks
Bill
It looks like the 'last_updated' field in 'log_shipping_primaries' in MSDB
isn't getting updated.
Whey would that be the case?
Thanks
Bill
"bill" <belgie@.datamti.com> wrote in message
news:e$lobJOeFHA.2128@.TK2MSFTNGP14.phx.gbl...
> I have successfully configured log shipping, and verified that the standby
> server is being updated correctly.
> There is one job named "Log Shipping Alert Job - Backup" which fails every
> minute.
> In the job history, it says:
> 'The job shipping source SERVERNAME.DATABASE has not backed up for 190
> minutes. [SQLSTATE 42000] [Error 14420] The step failed.'
> I guess that this can be disregarded, but supervisors get nervous about
> errors, and suspect that the log shipping is not reliable.
> How can I make this job succeed?
> Thanks
> Bill
>
>
|||Microsoft say:
"Thresholds
The DBA maintaining the SQL Server environment needs to know if log shipping becomes too far out of sync and/or may be failing. The two parameters to configure are: backup alert threshold and out of sync alert threshold. The alerts are triggered by error messages 14420 and 14421, respectively, and can be changed. After log shipping is installed, access the parameters by selecting the properties of the log shipping pair listed under the Log Shipping Monitor. "
Regards,
Milovan

Log Shipping Copy Job reports successful, but copies nothing

I have set up log shipping between 2 servers but cant get it to
function correctly.
Everything seems to be fine, the backup job happens, dumping the
transaction log to the network share on the primary server(also the
monitoring server).
The copy job runs and completes in 1 second or less, but doesn't seem
to move anything even though it reports success.
If I look at the copy/restore history in the log shipping monitor, it
continuously says that the copy succeeded, but no files were moved and
the last file moved is first_file_00000000.trn
I have checked the settings for the shares, and they are both fine.
I am able to log into the secondary server using the Sql Agent account
and map to the network share on the primary server where the log files
are being created. No problem there.
If I manually move the files the restore job works fine.
The log_shipping_secondary table is getting updated by the restore job
too, and I am using sql authentication with the
log_shipping_monitor_probe account set to sysadmin and db_ownber in
msdb.
It seems to be just the copy jobs never seeming to find any files even
when they are there.
I am starting to go crazy! HELP!
-Gregory
Gregory,
usually when I troubleshoot this issue it is a problem with the setup of
directories and shares, which can be confusing in the wizard. Please can you
remove the existing maintenance plan and set up the directories/shares below
then recreate the plan.
You need a folder eg c:\ProductionLogs that is shared
(\\server\PRODUCTIONLOGS) on the production server.
You need a directory on the standby server c:\shippedlogs, not shared.
In the maintenance plan, the transaction log backup directory is
c:\ProductionLogs
The network sharename is \\server\PRODUCTIONLOGS
The destination is c:\shippedlogs.
HTH,
Paul Ibison
|||Log shipping gives me no love.
I tried as you suggested, created new network shares, deleted the
maintenance plan and recreated the plan using the info you supplied.
It continues to give me the same problem.
This is in the history for the job:
Executed as user: XXXXXXX\UserID. output
---
---
(null)
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998. The step succeeded..
So informational.
Beautiful huh?
Additional info:
The servers are not on a domain, they are both in a workgroup.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Gregory,
there isn't an easy way for me to debug this, and no offence to you but I'd
like to ensure this is not a setup issue.
Please can you send me screenshots of the options you select when you go
through the wizard?
TIA,
Paul Ibison

Log Shipping Copy Errors

We've been experiencing above average log shipping
log "copy" errors. This is the job in which the server
which the log is shipped to has a job on it that copies
the newest log files from the host server. For no apparent
reason, this job fails , but the next time it's run, it
works. Some times it fails 10 times in a row then catches
up all at once. Other times it's less frequent. Anyone
know what might cause this . Disk space and netowkr access
isn't an issue, we've checked that.
thanksTom
Do you have a delay between the source server backing up
the database and the log shipping picking it up? It may be
that the target database is trying to copy the source
backup before it is finished. When you have problems is it
at a busy time, when for instance the log may be larger
than normal?
Regards
John|||Whats the error message?
>--Original Message--
>We've been experiencing above average log shipping
>log "copy" errors. This is the job in which the server
>which the log is shipped to has a job on it that copies
>the newest log files from the host server. For no
apparent
>reason, this job fails , but the next time it's run, it
>works. Some times it fails 10 times in a row then catches
>up all at once. Other times it's less frequent. Anyone
>know what might cause this . Disk space and netowkr
access
>isn't an issue, we've checked that.
>thanks
>.
>

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.
NEVER MIND!! I just read knowledge base...DUH!! I forgot to upgrade to sp3
when i set up my testing environment.
"mrdj" wrote:

> the "Log Shipping alert job-restore" job keeps showing a status of "failed" ,
> however the databases are in sync.