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
Showing posts with label configuration. Show all posts
Showing posts with label configuration. Show all posts
Monday, March 26, 2012
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
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
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
Wednesday, March 21, 2012
Log Shipping NTFS compressed files WAN
If the compression attribute is set on both the source SQL Server directory
and the target SQL Server directory for a log shipping configuration, will
the log files be uncompressed and then recompressed when the copy is made?
Or will the files remain compressed while the copy is taking place? If
WIN2K and SS are smart enough to handle the latter, it will speed up the
transfer of files across a WAN link.This is purely an OS thing, nothing to do with SQL.
See the quote from the following KB article
http://support.microsoft.com/?kbid=251186
When you copy or move a compressed NTFS file to a different folder, NTFS
decompresses the file, copies or moves the file to the new location, and
then recompresses the file. This behavior occurs even when the file is
copied or moved between folders on the same computer. Compressed files are
also expanded before copying over the network, so NTFS compression does not
save network bandwidth
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Don Ferguson" <don@.nospamplease> wrote in message
news:eVy5m1wRDHA.1552@.TK2MSFTNGP10.phx.gbl...
If the compression attribute is set on both the source SQL Server directory
and the target SQL Server directory for a log shipping configuration, will
the log files be uncompressed and then recompressed when the copy is made?
Or will the files remain compressed while the copy is taking place? If
WIN2K and SS are smart enough to handle the latter, it will speed up the
transfer of files across a WAN link.|||Thanks Jasper!
Your response and the KB link answers my question even if this has nothing
to do with SQL Server.
I guess I need to look into SQL LiteSpeed or SQLZIP for a possible solution.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OZtmZexRDHA.2128@.TK2MSFTNGP12.phx.gbl...
> This is purely an OS thing, nothing to do with SQL.
> See the quote from the following KB article
> http://support.microsoft.com/?kbid=251186
> When you copy or move a compressed NTFS file to a different folder, NTFS
> decompresses the file, copies or moves the file to the new location, and
> then recompresses the file. This behavior occurs even when the file is
> copied or moved between folders on the same computer. Compressed files are
> also expanded before copying over the network, so NTFS compression does
not
> save network bandwidth
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Don Ferguson" <don@.nospamplease> wrote in message
> news:eVy5m1wRDHA.1552@.TK2MSFTNGP10.phx.gbl...
> If the compression attribute is set on both the source SQL Server
directory
> and the target SQL Server directory for a log shipping configuration, will
> the log files be uncompressed and then recompressed when the copy is made?
> Or will the files remain compressed while the copy is taking place? If
> WIN2K and SS are smart enough to handle the latter, it will speed up the
> transfer of files across a WAN link.
>
>|||I'm a big fan of SQL Litespeed, does exactly what it says on the tin :-)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Don Ferguson" <don@.nospamplease> wrote in message
news:evh15xxRDHA.2316@.tk2msftngp13.phx.gbl...
Thanks Jasper!
Your response and the KB link answers my question even if this has nothing
to do with SQL Server.
I guess I need to look into SQL LiteSpeed or SQLZIP for a possible solution.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OZtmZexRDHA.2128@.TK2MSFTNGP12.phx.gbl...
> This is purely an OS thing, nothing to do with SQL.
> See the quote from the following KB article
> http://support.microsoft.com/?kbid=251186
> When you copy or move a compressed NTFS file to a different folder, NTFS
> decompresses the file, copies or moves the file to the new location, and
> then recompresses the file. This behavior occurs even when the file is
> copied or moved between folders on the same computer. Compressed files are
> also expanded before copying over the network, so NTFS compression does
not
> save network bandwidth
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Don Ferguson" <don@.nospamplease> wrote in message
> news:eVy5m1wRDHA.1552@.TK2MSFTNGP10.phx.gbl...
> If the compression attribute is set on both the source SQL Server
directory
> and the target SQL Server directory for a log shipping configuration, will
> the log files be uncompressed and then recompressed when the copy is made?
> Or will the files remain compressed while the copy is taking place? If
> WIN2K and SS are smart enough to handle the latter, it will speed up the
> transfer of files across a WAN link.
>
>
and the target SQL Server directory for a log shipping configuration, will
the log files be uncompressed and then recompressed when the copy is made?
Or will the files remain compressed while the copy is taking place? If
WIN2K and SS are smart enough to handle the latter, it will speed up the
transfer of files across a WAN link.This is purely an OS thing, nothing to do with SQL.
See the quote from the following KB article
http://support.microsoft.com/?kbid=251186
When you copy or move a compressed NTFS file to a different folder, NTFS
decompresses the file, copies or moves the file to the new location, and
then recompresses the file. This behavior occurs even when the file is
copied or moved between folders on the same computer. Compressed files are
also expanded before copying over the network, so NTFS compression does not
save network bandwidth
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Don Ferguson" <don@.nospamplease> wrote in message
news:eVy5m1wRDHA.1552@.TK2MSFTNGP10.phx.gbl...
If the compression attribute is set on both the source SQL Server directory
and the target SQL Server directory for a log shipping configuration, will
the log files be uncompressed and then recompressed when the copy is made?
Or will the files remain compressed while the copy is taking place? If
WIN2K and SS are smart enough to handle the latter, it will speed up the
transfer of files across a WAN link.|||Thanks Jasper!
Your response and the KB link answers my question even if this has nothing
to do with SQL Server.
I guess I need to look into SQL LiteSpeed or SQLZIP for a possible solution.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OZtmZexRDHA.2128@.TK2MSFTNGP12.phx.gbl...
> This is purely an OS thing, nothing to do with SQL.
> See the quote from the following KB article
> http://support.microsoft.com/?kbid=251186
> When you copy or move a compressed NTFS file to a different folder, NTFS
> decompresses the file, copies or moves the file to the new location, and
> then recompresses the file. This behavior occurs even when the file is
> copied or moved between folders on the same computer. Compressed files are
> also expanded before copying over the network, so NTFS compression does
not
> save network bandwidth
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Don Ferguson" <don@.nospamplease> wrote in message
> news:eVy5m1wRDHA.1552@.TK2MSFTNGP10.phx.gbl...
> If the compression attribute is set on both the source SQL Server
directory
> and the target SQL Server directory for a log shipping configuration, will
> the log files be uncompressed and then recompressed when the copy is made?
> Or will the files remain compressed while the copy is taking place? If
> WIN2K and SS are smart enough to handle the latter, it will speed up the
> transfer of files across a WAN link.
>
>|||I'm a big fan of SQL Litespeed, does exactly what it says on the tin :-)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Don Ferguson" <don@.nospamplease> wrote in message
news:evh15xxRDHA.2316@.tk2msftngp13.phx.gbl...
Thanks Jasper!
Your response and the KB link answers my question even if this has nothing
to do with SQL Server.
I guess I need to look into SQL LiteSpeed or SQLZIP for a possible solution.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OZtmZexRDHA.2128@.TK2MSFTNGP12.phx.gbl...
> This is purely an OS thing, nothing to do with SQL.
> See the quote from the following KB article
> http://support.microsoft.com/?kbid=251186
> When you copy or move a compressed NTFS file to a different folder, NTFS
> decompresses the file, copies or moves the file to the new location, and
> then recompresses the file. This behavior occurs even when the file is
> copied or moved between folders on the same computer. Compressed files are
> also expanded before copying over the network, so NTFS compression does
not
> save network bandwidth
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Don Ferguson" <don@.nospamplease> wrote in message
> news:eVy5m1wRDHA.1552@.TK2MSFTNGP10.phx.gbl...
> If the compression attribute is set on both the source SQL Server
directory
> and the target SQL Server directory for a log shipping configuration, will
> the log files be uncompressed and then recompressed when the copy is made?
> Or will the files remain compressed while the copy is taking place? If
> WIN2K and SS are smart enough to handle the latter, it will speed up the
> transfer of files across a WAN link.
>
>
Log shipping Monitor server
Hi,
Can separate monitor server for SQL 2000 Log Shipping
high-availability configuration be SQL 2000 Standard
Edtion?
Or must be SQL 2000 Server Enterprise Edition?
Thanks for answers
MilanYou cant have log shipping for SQL Standard, not quite sure what ur
question is?
Regards
J
"Milan Ojstersek" <milan.ojstersek@.hermes-plus.si> wrote in message
news:083101c34c5d$25531fe0$a601280a@.phx.gbl...
> Hi,
> Can separate monitor server for SQL 2000 Log Shipping
> high-availability configuration be SQL 2000 Standard
> Edtion?
> Or must be SQL 2000 Server Enterprise Edition?
> Thanks for answers
> Milan
Can separate monitor server for SQL 2000 Log Shipping
high-availability configuration be SQL 2000 Standard
Edtion?
Or must be SQL 2000 Server Enterprise Edition?
Thanks for answers
MilanYou cant have log shipping for SQL Standard, not quite sure what ur
question is?
Regards
J
"Milan Ojstersek" <milan.ojstersek@.hermes-plus.si> wrote in message
news:083101c34c5d$25531fe0$a601280a@.phx.gbl...
> Hi,
> Can separate monitor server for SQL 2000 Log Shipping
> high-availability configuration be SQL 2000 Standard
> Edtion?
> Or must be SQL 2000 Server Enterprise Edition?
> Thanks for answers
> Milan
log shipping monitor report error
I have SQL Server 2005 log shipping setup with primary/secondary configuration. I can confirm from the logs that log shipping is working without issue, however, reports generated from the monitor server show this message:
Violation of PRIMARY KEY constraint 'PK__#log_shipping_mo__3ABBDC91'. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'. The statement has been terminated.
There is nothing special about the configuration. Any ideas?
Clear out the log on Log shipping monitor and ensure the log shipping is working properly on secondary server too.|||Log shipping is working properly on the secondary server. The error is generated when I try to view the Log Shipping Status report on the secondary server. The monitor server instance is also throwing incorrect alerts.|||I think the problem you are seeing is related to some old information being present in the tables used to store log shipping configuration. There are some scenarios where this can happen and it causes the problem you reported in your first post. We are working on correcting this in a future release.
As you can tell from the error, the problem is caused by an insert to a temp table causing a PK constraint violation. The PK for the temp table is server name and database name. This error is normally caused by old configuration being present in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary. You can view the contents of these tables directly (in msdb) or use some supplied help SP's (see BOL topic titled "Log Shipping Tables and Stored Procedures").
The workaround is to remove the old rows from log_shipping_monitor_primary and/or log_shipping_monitor_secondary tables. Can you determine that you do indeed have stale data in the log shipping tables. If this is the case I can work with you on how to remove the old rows.
The old configuration data is probably causing the incorrect alerts you are seeing.
|||Hi, Is that possible for you to show how to delete the old data in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary.
Thank you
|||Hi Mark,
I am getting a similar error when I try to run the report on the primary server. The report on the secondary server which is also the monitor runs fine.
I queried the tables mentioned by you and got one row in each. FYI, I have only one DB on the primary server being log shipped to the secondary.
For,
select * from dbo.log_shipping_monitor_secondary
I am geeting null values for last_copied_file, last_copied_date, last_copied_date_utc and last restored_file. However, I know that the log shipping is working well for the database.
So How do I go about correcting the report? Should I delete the row in log_shipping_monitor_secondary? In log_shipping_monitor_primary the information is up to date and correct.
Thanks in anticipation.
Amit
sqllog shipping monitor report error
I have SQL Server 2005 log shipping setup with primary/secondary configuration. I can confirm from the logs that log shipping is working without issue, however, reports generated from the monitor server show this message:
Violation of PRIMARY KEY constraint 'PK__#log_shipping_mo__3ABBDC91'. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'. The statement has been terminated.
There is nothing special about the configuration. Any ideas?
Clear out the log on Log shipping monitor and ensure the log shipping is working properly on secondary server too.|||Log shipping is working properly on the secondary server. The error is generated when I try to view the Log Shipping Status report on the secondary server. The monitor server instance is also throwing incorrect alerts.|||I think the problem you are seeing is related to some old information being present in the tables used to store log shipping configuration. There are some scenarios where this can happen and it causes the problem you reported in your first post. We are working on correcting this in a future release.
As you can tell from the error, the problem is caused by an insert to a temp table causing a PK constraint violation. The PK for the temp table is server name and database name. This error is normally caused by old configuration being present in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary. You can view the contents of these tables directly (in msdb) or use some supplied help SP's (see BOL topic titled "Log Shipping Tables and Stored Procedures").
The workaround is to remove the old rows from log_shipping_monitor_primary and/or log_shipping_monitor_secondary tables. Can you determine that you do indeed have stale data in the log shipping tables. If this is the case I can work with you on how to remove the old rows.
The old configuration data is probably causing the incorrect alerts you are seeing.
|||Hi, Is that possible for you to show how to delete the old data in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary.
Thank you
|||Hi Mark,
I am getting a similar error when I try to run the report on the primary server. The report on the secondary server which is also the monitor runs fine.
I queried the tables mentioned by you and got one row in each. FYI, I have only one DB on the primary server being log shipped to the secondary.
For,
select * from dbo.log_shipping_monitor_secondary
I am geeting null values for last_copied_file, last_copied_date, last_copied_date_utc and last restored_file. However, I know that the log shipping is working well for the database.
So How do I go about correcting the report? Should I delete the row in log_shipping_monitor_secondary? In log_shipping_monitor_primary the information is up to date and correct.
Thanks in anticipation.
Amit
log shipping monitor report error
I have SQL Server 2005 log shipping setup with primary/secondary configuration. I can confirm from the logs that log shipping is working without issue, however, reports generated from the monitor server show this message:
Violation of PRIMARY KEY constraint 'PK__#log_shipping_mo__3ABBDC91'. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'. The statement has been terminated.
There is nothing special about the configuration. Any ideas?
Clear out the log on Log shipping monitor and ensure the log shipping is working properly on secondary server too.|||Log shipping is working properly on the secondary server. The error is generated when I try to view the Log Shipping Status report on the secondary server. The monitor server instance is also throwing incorrect alerts.|||I think the problem you are seeing is related to some old information being present in the tables used to store log shipping configuration. There are some scenarios where this can happen and it causes the problem you reported in your first post. We are working on correcting this in a future release.
As you can tell from the error, the problem is caused by an insert to a temp table causing a PK constraint violation. The PK for the temp table is server name and database name. This error is normally caused by old configuration being present in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary. You can view the contents of these tables directly (in msdb) or use some supplied help SP's (see BOL topic titled "Log Shipping Tables and Stored Procedures").
The workaround is to remove the old rows from log_shipping_monitor_primary and/or log_shipping_monitor_secondary tables. Can you determine that you do indeed have stale data in the log shipping tables. If this is the case I can work with you on how to remove the old rows.
The old configuration data is probably causing the incorrect alerts you are seeing.
|||Hi, Is that possible for you to show how to delete the old data in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary.
Thank you
|||Hi Mark,
I am getting a similar error when I try to run the report on the primary server. The report on the secondary server which is also the monitor runs fine.
I queried the tables mentioned by you and got one row in each. FYI, I have only one DB on the primary server being log shipped to the secondary.
For,
select * from dbo.log_shipping_monitor_secondary
I am geeting null values for last_copied_file, last_copied_date, last_copied_date_utc and last restored_file. However, I know that the log shipping is working well for the database.
So How do I go about correcting the report? Should I delete the row in log_shipping_monitor_secondary? In log_shipping_monitor_primary the information is up to date and correct.
Thanks in anticipation.
Amit
log shipping monitor report error
I have SQL Server 2005 log shipping setup with primary/secondary configuration. I can confirm from the logs that log shipping is working without issue, however, reports generated from the monitor server show this message:
Violation of PRIMARY KEY constraint 'PK__#log_shipping_mo__3ABBDC91'. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'. The statement has been terminated.
There is nothing special about the configuration. Any ideas?
Clear out the log on Log shipping monitor and ensure the log shipping is working properly on secondary server too.|||Log shipping is working properly on the secondary server. The error is generated when I try to view the Log Shipping Status report on the secondary server. The monitor server instance is also throwing incorrect alerts.|||I think the problem you are seeing is related to some old information being present in the tables used to store log shipping configuration. There are some scenarios where this can happen and it causes the problem you reported in your first post. We are working on correcting this in a future release.
As you can tell from the error, the problem is caused by an insert to a temp table causing a PK constraint violation. The PK for the temp table is server name and database name. This error is normally caused by old configuration being present in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary. You can view the contents of these tables directly (in msdb) or use some supplied help SP's (see BOL topic titled "Log Shipping Tables and Stored Procedures").
The workaround is to remove the old rows from log_shipping_monitor_primary and/or log_shipping_monitor_secondary tables. Can you determine that you do indeed have stale data in the log shipping tables. If this is the case I can work with you on how to remove the old rows.
The old configuration data is probably causing the incorrect alerts you are seeing.
|||Hi, Is that possible for you to show how to delete the old data in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary.
Thank you
|||Hi Mark,
I am getting a similar error when I try to run the report on the primary server. The report on the secondary server which is also the monitor runs fine.
I queried the tables mentioned by you and got one row in each. FYI, I have only one DB on the primary server being log shipped to the secondary.
For,
select * from dbo.log_shipping_monitor_secondary
I am geeting null values for last_copied_file, last_copied_date, last_copied_date_utc and last restored_file. However, I know that the log shipping is working well for the database.
So How do I go about correcting the report? Should I delete the row in log_shipping_monitor_secondary? In log_shipping_monitor_primary the information is up to date and correct.
Thanks in anticipation.
Amit
Monday, March 12, 2012
Log shipping from SQL2000EE SP3 to SP4
Hi,
Source ==> SQL Server 2000 EE SP3
Target ==> SQL Server 2000 EE SP4
Does automated log shipping work for this configuration? Or log
shipping requires same service pack levels?
Thanks,
zrb
That config should work (although it is preferable to have the same
service pack level on both servers so that if you have to point your
client code at the secondary server there are no post-SP3 bug surprises).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
zrajbun@.hotmail.com wrote:
>Hi,
>Source ==> SQL Server 2000 EE SP3
>Target ==> SQL Server 2000 EE SP4
>Does automated log shipping work for this configuration? Or log
>shipping requires same service pack levels?
>Thanks,
>zrb
>
>
|||Thank you Mike. I think I'll go with the same service pack levels,
keeping SP3.
b4n
zrb
Source ==> SQL Server 2000 EE SP3
Target ==> SQL Server 2000 EE SP4
Does automated log shipping work for this configuration? Or log
shipping requires same service pack levels?
Thanks,
zrb
That config should work (although it is preferable to have the same
service pack level on both servers so that if you have to point your
client code at the secondary server there are no post-SP3 bug surprises).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
zrajbun@.hotmail.com wrote:
>Hi,
>Source ==> SQL Server 2000 EE SP3
>Target ==> SQL Server 2000 EE SP4
>Does automated log shipping work for this configuration? Or log
>shipping requires same service pack levels?
>Thanks,
>zrb
>
>
|||Thank you Mike. I think I'll go with the same service pack levels,
keeping SP3.
b4n
zrb
Log shipping from SQL2000EE SP3 to SP4
Hi,
Source ==> SQL Server 2000 EE SP3
Target ==> SQL Server 2000 EE SP4
Does automated log shipping work for this configuration? Or log
shipping requires same service pack levels?
Thanks,
zrbThat config should work (although it is preferable to have the same
service pack level on both servers so that if you have to point your
client code at the secondary server there are no post-SP3 bug surprises).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
zrajbun@.hotmail.com wrote:
>Hi,
>Source ==> SQL Server 2000 EE SP3
>Target ==> SQL Server 2000 EE SP4
>Does automated log shipping work for this configuration? Or log
>shipping requires same service pack levels?
>Thanks,
>zrb
>
>|||Thank you Mike. I think I'll go with the same service pack levels,
keeping SP3.
b4n
zrb
Source ==> SQL Server 2000 EE SP3
Target ==> SQL Server 2000 EE SP4
Does automated log shipping work for this configuration? Or log
shipping requires same service pack levels?
Thanks,
zrbThat config should work (although it is preferable to have the same
service pack level on both servers so that if you have to point your
client code at the secondary server there are no post-SP3 bug surprises).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
zrajbun@.hotmail.com wrote:
>Hi,
>Source ==> SQL Server 2000 EE SP3
>Target ==> SQL Server 2000 EE SP4
>Does automated log shipping work for this configuration? Or log
>shipping requires same service pack levels?
>Thanks,
>zrb
>
>|||Thank you Mike. I think I'll go with the same service pack levels,
keeping SP3.
b4n
zrb
Log shipping from SQL2000EE SP3 to SP4
Hi,
Source ==> SQL Server 2000 EE SP3
Target ==> SQL Server 2000 EE SP4
Does automated log shipping work for this configuration? Or log
shipping requires same service pack levels?
Thanks,
zrbThis is a multi-part message in MIME format.
--090403030908040509010200
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
That config should work (although it is preferable to have the same
service pack level on both servers so that if you have to point your
client code at the secondary server there are no post-SP3 bug surprises).
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
zrajbun@.hotmail.com wrote:
>Hi,
>Source ==> SQL Server 2000 EE SP3
>Target ==> SQL Server 2000 EE SP4
>Does automated log shipping work for this configuration? Or log
>shipping requires same service pack levels?
>Thanks,
>zrb
>
>
--090403030908040509010200
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>That config should work (although it is preferable to have the same
service pack level on both servers so that if you have to point your
client code at the secondary server there are no post-SP3 bug
surprises).</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:zrajbun@.hotmail.com">zrajbun@.hotmail.com</a> wrote:
<blockquote
cite="mid1124169030.671658.100080@.g49g2000cwa.googlegroups.com"
type="cite">
<pre wrap="">Hi,
Source ==> SQL Server 2000 EE SP3
Target ==> SQL Server 2000 EE SP4
Does automated log shipping work for this configuration? Or log
shipping requires same service pack levels?
Thanks,
zrb
</pre>
</blockquote>
</body>
</html>
--090403030908040509010200--|||Thank you Mike. I think I'll go with the same service pack levels,
keeping SP3.
b4n
zrb
Source ==> SQL Server 2000 EE SP3
Target ==> SQL Server 2000 EE SP4
Does automated log shipping work for this configuration? Or log
shipping requires same service pack levels?
Thanks,
zrbThis is a multi-part message in MIME format.
--090403030908040509010200
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
That config should work (although it is preferable to have the same
service pack level on both servers so that if you have to point your
client code at the secondary server there are no post-SP3 bug surprises).
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
zrajbun@.hotmail.com wrote:
>Hi,
>Source ==> SQL Server 2000 EE SP3
>Target ==> SQL Server 2000 EE SP4
>Does automated log shipping work for this configuration? Or log
>shipping requires same service pack levels?
>Thanks,
>zrb
>
>
--090403030908040509010200
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>That config should work (although it is preferable to have the same
service pack level on both servers so that if you have to point your
client code at the secondary server there are no post-SP3 bug
surprises).</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:zrajbun@.hotmail.com">zrajbun@.hotmail.com</a> wrote:
<blockquote
cite="mid1124169030.671658.100080@.g49g2000cwa.googlegroups.com"
type="cite">
<pre wrap="">Hi,
Source ==> SQL Server 2000 EE SP3
Target ==> SQL Server 2000 EE SP4
Does automated log shipping work for this configuration? Or log
shipping requires same service pack levels?
Thanks,
zrb
</pre>
</blockquote>
</body>
</html>
--090403030908040509010200--|||Thank you Mike. I think I'll go with the same service pack levels,
keeping SP3.
b4n
zrb
Friday, March 9, 2012
Log Shipping ERROR HELP
Hi All
Can someone tell me what to do with this Error:
SQL Server Management Studio could not save the configuration of 'B1' as a Secondary.
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The specified @.server_name (A1) does not exist.
The specified @.server_name (A1) does not exist.
The specified @.server_name (A1) does not exist. (Microsoft SQL Server, Error: 14262)
I have two sql 2005 servers A1 and B1 but the databases are setup with sql 2000 compatibility.
A1 service accounts use local system account.
B1 service accounts use domain system account.
I can easily restore a database from a shared folder on A1.
I followed this article to setup log shipping.
http://deepakinsql.blogspot.com/2007/06/how-to-configure-log-shipping-in-sql.html
did anyone come across this problem?
Hi,Check SQL servername @. both the server. Run the below query and check whether the servername is A1 and B1.
Code Snippet
select @.@.servername
If A1 is not there in the first server then rename server to A1.
|||
As Vidhya said check the server name and try configuring log shipping using local system account for both the servers as in the given link
|||THANKS GUYS.
IT WAS THE SERVER NAME PROBLEM.
Log Shipping ERROR HELP
Hi All
Can someone tell me what to do with this Error:
SQL Server Management Studio could not save the configuration of 'B1' as a Secondary.
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The specified @.server_name (A1) does not exist.
The specified @.server_name (A1) does not exist.
The specified @.server_name (A1) does not exist. (Microsoft SQL Server, Error: 14262)
I have two sql 2005 servers A1 and B1 but the databases are setup with sql 2000 compatibility.
A1 service accounts use local system account.
B1 service accounts use domain system account.
I can easily restore a database from a shared folder on A1.
I followed this article to setup log shipping.
http://deepakinsql.blogspot.com/2007/06/how-to-configure-log-shipping-in-sql.html
did anyone come across this problem?
Hi,Check SQL servername @. both the server. Run the below query and check whether the servername is A1 and B1.
Code Snippet
select @.@.servername
If A1 is not there in the first server then rename server to A1.
|||
As Vidhya said check the server name and try configuring log shipping using local system account for both the servers as in the given link
|||THANKS GUYS.
IT WAS THE SERVER NAME PROBLEM.
Wednesday, March 7, 2012
Log shipping configuration - HELP
I'm having a difficult time making sence of the schedule SQL Server has
configured for my copy and restore jobs. I can't tie the monitor information
back to any of it.
My log shipping configuration does a backup every 120 minutes between 5AM
and 2:59AM; I run backups @. 3AM. The copy/load Frequency is set to 120
minutes with a load delay is 15 minutes.
Actual results are not what I expected, the log backups occur as expected
5AM, 7AM,9AM ect.., but the copy and restore jobs created on the secondary
are behind by 1 hour. Both the copy and restore jobs are executing on a 6AM,
8AM, 10AM schedule. Cany some one explain why this is happening? It is
causing an out of sync condition.
What effect does the load delay have on the restore job schedule.
Not sure it will help but look at
http://www.mindsdoor.net/SQLAdmin/LogShipping.html
If you want to replace it.
"Jerrick D.H" wrote:
> I'm having a difficult time making sence of the schedule SQL Server has
> configured for my copy and restore jobs. I can't tie the monitor information
> back to any of it.
> My log shipping configuration does a backup every 120 minutes between 5AM
> and 2:59AM; I run backups @. 3AM. The copy/load Frequency is set to 120
> minutes with a load delay is 15 minutes.
> Actual results are not what I expected, the log backups occur as expected
> 5AM, 7AM,9AM ect.., but the copy and restore jobs created on the secondary
> are behind by 1 hour. Both the copy and restore jobs are executing on a 6AM,
> 8AM, 10AM schedule. Cany some one explain why this is happening? It is
> causing an out of sync condition.
> What effect does the load delay have on the restore job schedule.
>
|||Nigel,
Thank you for taking the time to respond. I was using a similar process as
the one documented by mindsdoor, but switched to SQL automated process, and
I like it. Since I could not find a valid reason for the delay, I played
around with the job schedules until they woked they way I wanted them too.
"Nigel Rivett" wrote:
[vbcol=seagreen]
> Not sure it will help but look at
> http://www.mindsdoor.net/SQLAdmin/LogShipping.html
> If you want to replace it.
> "Jerrick D.H" wrote:
configured for my copy and restore jobs. I can't tie the monitor information
back to any of it.
My log shipping configuration does a backup every 120 minutes between 5AM
and 2:59AM; I run backups @. 3AM. The copy/load Frequency is set to 120
minutes with a load delay is 15 minutes.
Actual results are not what I expected, the log backups occur as expected
5AM, 7AM,9AM ect.., but the copy and restore jobs created on the secondary
are behind by 1 hour. Both the copy and restore jobs are executing on a 6AM,
8AM, 10AM schedule. Cany some one explain why this is happening? It is
causing an out of sync condition.
What effect does the load delay have on the restore job schedule.
Not sure it will help but look at
http://www.mindsdoor.net/SQLAdmin/LogShipping.html
If you want to replace it.
"Jerrick D.H" wrote:
> I'm having a difficult time making sence of the schedule SQL Server has
> configured for my copy and restore jobs. I can't tie the monitor information
> back to any of it.
> My log shipping configuration does a backup every 120 minutes between 5AM
> and 2:59AM; I run backups @. 3AM. The copy/load Frequency is set to 120
> minutes with a load delay is 15 minutes.
> Actual results are not what I expected, the log backups occur as expected
> 5AM, 7AM,9AM ect.., but the copy and restore jobs created on the secondary
> are behind by 1 hour. Both the copy and restore jobs are executing on a 6AM,
> 8AM, 10AM schedule. Cany some one explain why this is happening? It is
> causing an out of sync condition.
> What effect does the load delay have on the restore job schedule.
>
|||Nigel,
Thank you for taking the time to respond. I was using a similar process as
the one documented by mindsdoor, but switched to SQL automated process, and
I like it. Since I could not find a valid reason for the delay, I played
around with the job schedules until they woked they way I wanted them too.
"Nigel Rivett" wrote:
[vbcol=seagreen]
> Not sure it will help but look at
> http://www.mindsdoor.net/SQLAdmin/LogShipping.html
> If you want to replace it.
> "Jerrick D.H" wrote:
Subscribe to:
Posts (Atom)