Log Shipping will not restore tran logs on secondary server

We're planning to implement log shipping on our databases, and I have been toiling with it all weekend trying to get it to work on some test databases. The result is the same whether I do it via the wizard or manually via T-SQL.

I am using 3 servers, all SQL Server 2005 Standard SP1. All 3 SQL Servers are configured identically.

When I setup log shipping, it initializes with no problems. When it processes the first tran log file, it restores it with no problem. Every successive log file thereafter is not restored. No errors are generated. The only outright indication of a problem is that the monitor server shows that there has not been a recent restore.

The backup and copy both suceed. The restore claims to suceed. If I review the job history for each step, it says that it skipped the log file and then reports that it did not fina any log files to restore.

Message
2006-11-06 05:00:01.92 Skipped log backup file. Secondary DB: 'MyDemo', File: 'D:\MSSQL\Backup\MyDemo\MyDemo_20061106115619.trn'

Message
2006-11-06 05:00:01.95 Could not find a log backup file that could be applied to secondary database 'MyDemo'.
2006-11-06 05:00:01.96 The restore operation was successful. Secondary Database: 'MyDemo', Number of log backup files restored: 0

Any ideas or suggestions?

A little more info for you. I can manually restore the successive log file backups without any errors. However, the output says that there were 0 pages processed. The successive log files are being created empty. Well, these are test databases, so there are no active transactions ocurring, so no big surprise, right? I updated a lot of data in the tables, and the next log file is ....... empty.

So even with committed transactions ocurring, the tran log backup is still empty.

|||

I found the problem. It was a security setting that the Log Shipping wizard does not give you the ability to change. It defaulted to using Windows security (the account used by SQL Agent) for the connection to the Monitor server to update the state of the restore process. Because we use local system accounts, not domain accounts, for SQL Agent, it was not able to update the Monitor.

My scripts were based off of the first log shipping instance that I set up, so the option to change it there wasn’t included in the script generated by SQL Server. Since I originally set it up with the primary, secondary, and monitor on my local machine, it worked fine.

In the wizard, there is a setting for the account that the monitor should use, but it only affects the primary's connection to the monitor, not the secondary's connection.

Log Shipping Transaction Logs.

Hi,

We currently have a couple a large Databases running on SQL 2000 SP3 Clustered Windows 2000 SP3 environment.

Log Shipping is enabled for both databases shipping to a Standalone SQL 2000 SP3 Windows 2000 SP3 box.

Log Shipping occurs every 15 mins with the Transaction Files on average being no more than 500KB in size. However, every now and then a Transaction Log comes through and it can be as big as 3.52GB.

Not sure why this is happening. Anyone got any ideas?

Regards

Paul TowlerIs this after substantial activity or reindexing?|||i think auto shrink or some other job is enabled
if iam right, the auto shrink or job gets activated and the step goes thru
which will obviously create transaction file with huge size.|||Thanks for your replies.

There is a Job to Optimise the Database which does re-index the database. I presume all the indexing changes count as changes and therefore shipped as one big Transacton file.

Hopefully there is a way to prevent these hugh Transaction Files without turning off the indexing job.

Regards
Paul Towlersql

log shipping question on two databases

Hi,
I created 2 log shipping plans at the same time, for database dbA and dbB
respectively, to ship logs to antother database server.
All the log transaction schedule and location are the same.
However, only log shipping plan for dbA succeed.
Is there any restriction on log shipping for 2 databases at the same time?
Thanks for help.
Jason
No, there is no such restriction. You can can configure two databases from
the same instance for log shipping.
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Jason Huang" wrote:

> Hi,
> I created 2 log shipping plans at the same time, for database dbA and dbB
> respectively, to ship logs to antother database server.
> All the log transaction schedule and location are the same.
> However, only log shipping plan for dbA succeed.
> Is there any restriction on log shipping for 2 databases at the same time?
> Thanks for help.
>
> Jason
>
>

log shipping question on two databases

Hi,
I created 2 log shipping plans at the same time, for database dbA and dbB
respectively, to ship logs to antother database server.
All the log transaction schedule and location are the same.
However, only log shipping plan for dbA succeed.
Is there any restriction on log shipping for 2 databases at the same time?
Thanks for help.
JasonNo, there is no such restriction. You can can configure two databases from
the same instance for log shipping.
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Jason Huang" wrote:

> Hi,
> I created 2 log shipping plans at the same time, for database dbA and dbB
> respectively, to ship logs to antother database server.
> All the log transaction schedule and location are the same.
> However, only log shipping plan for dbA succeed.
> Is there any restriction on log shipping for 2 databases at the same time?
> Thanks for help.
>
> Jason
>
>

log shipping question on two databases

Hi,
I created 2 log shipping plans at the same time, for database dbA and dbB
respectively, to ship logs to antother database server.
All the log transaction schedule and location are the same.
However, only log shipping plan for dbA succeed.
Is there any restriction on log shipping for 2 databases at the same time?
Thanks for help.
JasonNo, there is no such restriction. You can can configure two databases from
the same instance for log shipping.
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Jason Huang" wrote:
> Hi,
> I created 2 log shipping plans at the same time, for database dbA and dbB
> respectively, to ship logs to antother database server.
> All the log transaction schedule and location are the same.
> However, only log shipping plan for dbA succeed.
> Is there any restriction on log shipping for 2 databases at the same time?
> Thanks for help.
>
> Jason
>
>

Log Shipping Question

Hi Everyone,
The logs that are copied to the standby server, what is the file that has a
..tuf extension?
Thanks in advance
Larry
Its the undo file when you setup log shipping in standby mode.. Look at undo
file in the restore database section in BOL
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:E4DD40E8-593D-4787-BA5F-F2DC8B25CA64@.microsoft.com...
> Hi Everyone,
> The logs that are copied to the standby server, what is the file that has
a
> .tuf extension?
> Thanks in advance
> Larry

Log Shipping Question

Using Log shipping via SQL Ent Edition, do all your users have to be disconnected from the destination server the logs are being pushed to?

Say, user x is pulling a report from the server, and it takes 2 minutes, after 1 minutes the scedualed log ship needs to take place, will the log ship fail? will the user get kicked off? or will the logs be applied like nothing happend since its read only?

Anyones that used log shipping alot let me know! Thanks

ChrisI believe because you leave the destination database in recoverable mode, users can not query it - see links for more details

http://support.microsoft.com/default.aspx?scid=kb;en-us;314515

http://www.sql-server-performance.com/sql_server_log_shipping.asp

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx

HTH|||And also this Q&A - http://www.sql-server-performance.com/q&a37.asp|||With Log Shipping, the destination db's are read only. Your users can query them as much as they like - the only stipulation is that they can't have an active connection to a database while it's logs are being restored - I simply have a process that kills any connections to the db before I restore the logs - and that takes care of the issue. Hope this helps.

Nick DeAngelo|||Refer to the link provided by me to kill those SPIDs before restoring the log.|||Thanks for pointing that out Nick, I forgot about that

Log Shipping Question

Hi Everyone,
The logs that are copied to the standby server, what is the file that has a
.tuf extension?
Thanks in advance
LarryIts the undo file when you setup log shipping in standby mode.. Look at undo
file in the restore database section in BOL
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:E4DD40E8-593D-4787-BA5F-F2DC8B25CA64@.microsoft.com...
> Hi Everyone,
> The logs that are copied to the standby server, what is the file that has
a
> .tuf extension?
> Thanks in advance
> Larry

Log Shipping problem

I have several SQL Server 2000 SP4 instances on Windows 2003 Advanced Server and Datacenter Edition shipping logs to a SQL Server 2000 SP4 instance on Windows 2003 Advanced Server. All of the log shipping pairs work fine except in the case of one instance. When I set up a log shipping pair from it, the maintenance plan, jobs and entries in the system tables are created successfuly, the initial database (which is small in my example) is backed up as part of the log shipping setup, copied to the secondary server and restored. But when the "copy" job executes in the secondary server, it doesn't copy the transaction log backups from the backup server. Other log shipping pairs established from other primaries to this secondary server work fine, so I know it is not a communications problem between this particular primary and the secondary server. The "copy" job indicates that it succeeds, but it doesn't do anything. I use LiteSpeed 2005 both on the primaries and on the secondary server. In the secondary server I have LiteSpeed's NCS (Native Command Substitution) installed, since the Log Shipping maintenance plans cannot be converted to LiteSpeed. Again, this works fine for all other instances. The configuration settings of the problematic primary are analogous to the configuration settings of the other instances. Log shipping used to work from the problematic primary to other secondary server, but not now. I suspect that this has something to do with inconsistencies in some system tables in the primary server. Has anyone a hint to offer?

If you are using the third party tool, have you contacted the vendor in first place.

If it is a SQL Server log shipping, then I would suggest to check SQL error log and event logs on both the servers to ensure there is no issue with SQL components, also you might take help of network admin to see why this is happenning with a network trace etc.

Log Shipping Problem

I am implementing Log Shipping. I have the transaction logs copying to
the standby server and applying to the standby database with no errors.

However, I am having problems with the role change. I am using the
following article/link as my guide:
http://msdn.microsoft.com/library/d..._1_013_9t0p.asp

The DTS package that I am to create to copy the logins from the current
primary server to the current secondary server is not working.

The job that I created (that encompasses BCP Out, Copy File, and
Transfer Logins) fails at the Transfer Logins step. That is the step
that calls the DTS package. Even though the Copy File step shows as
'successful', the file is definitely not on the standby server.

Here is the error message from the Transfer Login step:
Executed as user: SERVERNAME\Administrator. DTSRun: Loading...
DTSRun: Executing... DTSRun OnStart:
DTSStep_OMWCustomTasks.OMWTransferLogins_1 DTSRun OnError:
DTSStep_OMWCustomTasks.OMWTransferLogins_1, Error = -2147467259
(80004005) Error string: Unspecified error Error source:
Microsoft Data Transformation Services (DTS) Package Help file:
sqldts80.hlp Help context: 700 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 0 (0) Error
string: Unspecified error Error source: Microsoft Data
Transformation Services (DTS) Package Help file: sqldts80.hlp
Help context: 700 DTSRun OnFinish:
DTSStep_OMWCustomTasks.OMWTransferLogins_1 DTSRun: Package execution
complete. Process Exit Code 1. The step failed.

Both servers are running Server 2003. Both databases are SQL Server
2000 with SP3.

I have followed the steps in the article. Am I missing something? Or,
have I misinterpreted something? I would appreciate any help you could
provide.

Thanks in advance,
JennieI purchased Allan Hirt's "SQL Server 2000 High Availability" last
night. It has an entire chapter (72 pages!) devoted to log shipping. It
has slightly different instructions for setting up the
post-configuration tasks - as compared to the Microsoft article that I
had been following. His instructions work!

If you are implementing log shipping, I highly recommend this book!

Jennie

Log Shipping Monitor Shows Problems when there aren't any

I Have setup a pair of server with log shipping, it works perfectly, logs are
backed up every 2 mins and restored succesfully to the destination server. My
only problem is that the monitor (running on the backup server) thinks that
things are not working correctly. It shows the correct times for the copy and
load jobs but the backup job time never gets updated. I have read the MS KB
article 292586 which states that this could be a permissions issue that the
source server does not have permissions to update the msdb database on the
destination server, however I have tried changing things in this area by
having the sql account running with full admin privs (both domain admin and
sql admin) this didnt work, I also tried running sql in mixed auth mode and
then configurung log shipping to use SQL authentication, it correctly created
the new log_shipping_monitor account, but still the log shipping monitor
refuses to work. Can somebody help me before I pull all my hair out.
Brett
Hello Brett,
My suggestion is that you reset password for log_shipping_monitor_probe
account on all shipping databases (both secondary and primary) and removed
and
reapplied permissions to the log shipping tables on both primary and
secondary servers. If log_shipping_monitor_prob does not exist, create the
login, set the default
database to msdb, and grant it access to msdb.
On both Primary / Secondary server, right click the log shipping database,
select properties. In the general tab, click the details button for log
shipping. Select "Use SQL Server Authentication" and change the password
to log_shipping_monitor_prob account.
More related information:
321247 How To Configure Security for Log Shipping
http://support.microsoft.com/?id=321247
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Log Shipping Monitor Shows Problems when there aren't any
>thread-index: AcX4xD/DRKnfVG7nSZmLj/7UPPi8Vg==
>X-WBNR-Posting-Host: 194.109.239.116
>From: "=?Utf-8?B?QnJldHQgQ2Fycg==?=" <brettcarr@.news.postalias>
>Subject: Log Shipping Monitor Shows Problems when there aren't any
>Date: Sun, 4 Dec 2005 03:17:01 -0800
>Lines: 15
>Message-ID: <0E563B60-3095-4635-BC0A-3153BEB87CDB@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.replication:67206
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>I Have setup a pair of server with log shipping, it works perfectly, logs
are
>backed up every 2 mins and restored succesfully to the destination server.
My
>only problem is that the monitor (running on the backup server) thinks
that
>things are not working correctly. It shows the correct times for the copy
and
>load jobs but the backup job time never gets updated. I have read the MS
KB
>article 292586 which states that this could be a permissions issue that
the
>source server does not have permissions to update the msdb database on the
>destination server, however I have tried changing things in this area by
>having the sql account running with full admin privs (both domain admin
and
>sql admin) this didnt work, I also tried running sql in mixed auth mode
and
>then configurung log shipping to use SQL authentication, it correctly
created
>the new log_shipping_monitor account, but still the log shipping monitor
>refuses to work. Can somebody help me before I pull all my hair out.
>Brett
>
|||"Peter Yang [MSFT]" wrote:

> Hello Brett,
> My suggestion is that you reset password for log_shipping_monitor_probe
> account on all shipping databases (both secondary and primary) and removed
> and
> reapplied permissions to the log shipping tables on both primary and
> secondary servers. If log_shipping_monitor_prob does not exist, create the
> login, set the default
> database to msdb, and grant it access to msdb.
> On both Primary / Secondary server, right click the log shipping database,
> select properties. In the general tab, click the details button for log
> shipping. Select "Use SQL Server Authentication" and change the password
> to log_shipping_monitor_prob account.
>
Ok. I've done the following:
1. log_shipping_monitor_prob did not exist on the primary server so I
created it.
2. I reset the password for log_shipping_monitor_prob on both primary and
secondary.
3. On both primary and secondary servers I have set
log_shipping_monitor_prob to have select,insert,update and delete permissions
on all tables starting with log_shipping in the msdb database.
However I'm afraid the 'Last Backup' date and time listed in the monitor is
still the date and time I set it up originally. The Last copy and Last
restore dates are still being updated correctly.
Brett..
|||Hello Brett,
Going forward, I'd like to confirm if Windows Authentication option is
selected on the Specify The Log Shipping Monitor Server Information screen
during log shipping setup.
If so, you may want to check if the SQL Server Service startup account on
the primary server of a log shipping pair does not have sufficient
privileges to update table msdb..log_shipping_primaries on the monitor
server.
Also, please check if the value in primary_server_name column in
log_shipping_primaries does not match the <@.@.servername> of the primary
server.
If the issue persists, please use SQL profiler on the monitor server to see
if the update operation on log_shipping_primaries occurs or if there is any
related errors in error log.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Log Shipping Monitor Shows Problems when there aren't any
>thread-index: AcX5iaV+Rf+MfqKZTvqVdLuPrgGVlw==
>X-WBNR-Posting-Host: 193.0.2.180
>From: "=?Utf-8?B?QnJldHQgQ2Fycg==?=" <brettcarr@.news.postalias>
>References: <0E563B60-3095-4635-BC0A-3153BEB87CDB@.microsoft.com>
<VAQE#gX#FHA.1236@.TK2MSFTNGXA02.phx.gbl>[vbcol=seagreen]
>Subject: RE: Log Shipping Monitor Shows Problems when there aren't any
>Date: Mon, 5 Dec 2005 02:50:03 -0800
>Lines: 35
>Message-ID: <33C3B088-9CA3-4C3E-98B1-86E784833DDC@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.replication:67231
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>"Peter Yang [MSFT]" wrote:
removed[vbcol=seagreen]
the[vbcol=seagreen]
database,[vbcol=seagreen]
password
>Ok. I've done the following:
>1. log_shipping_monitor_prob did not exist on the primary server so I
>created it.
>2. I reset the password for log_shipping_monitor_prob on both primary and
>secondary.
>3. On both primary and secondary servers I have set
>log_shipping_monitor_prob to have select,insert,update and delete
permissions
>on all tables starting with log_shipping in the msdb database.
>However I'm afraid the 'Last Backup' date and time listed in the monitor
is
>still the date and time I set it up originally. The Last copy and Last
>restore dates are still being updated correctly.
>Brett..
>
>

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

sql

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

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

Hello,
I am just setting up log shipping on our new web servers. I have S1
primary, S2 secondary and S3 monitoring. The log shipping is working OK.
Logs are being backed up on S1, being copied to S2 and then loaded
successfully.
In my Log Shipping Monitor, on the Log Shipping Pair Properties Status tab,
the last backup file is still showing first_file_00000000000.trn. The last
file copied and Last file loaded on the are correct. When I am viewing the
monitor thru' EM on S3, and on the Source tab try to view the Backup
Schedule, the button is not disabled but does not display anything. If I
view the monitor thru' EM on S1 and view the Backup Schedule it displays the
schedule.
The only thing I have noticed is that the graphic at the top of the monitor
is showing (LOCAL).dbname --> S2.dbname. I would have expected it to show
S1.dbname --> S2.dbname. Is it trying to look at the backup on (LOCAL) so
looking on S3 which is just the monitoring server.
Anyone else seen this? Thanks for any help you can give me.
ClaireB
Sorry - meant to add I am looking at the graphic on S3.
"ClaireB" wrote:

> Hello,
> I am just setting up log shipping on our new web servers. I have S1
> primary, S2 secondary and S3 monitoring. The log shipping is working OK.
> Logs are being backed up on S1, being copied to S2 and then loaded
> successfully.
> In my Log Shipping Monitor, on the Log Shipping Pair Properties Status tab,
> the last backup file is still showing first_file_00000000000.trn. The last
> file copied and Last file loaded on the are correct. When I am viewing the
> monitor thru' EM on S3, and on the Source tab try to view the Backup
> Schedule, the button is not disabled but does not display anything. If I
> view the monitor thru' EM on S1 and view the Backup Schedule it displays the
> schedule.
> The only thing I have noticed is that the graphic at the top of the monitor
> is showing (LOCAL).dbname --> S2.dbname. I would have expected it to show
> S1.dbname --> S2.dbname. Is it trying to look at the backup on (LOCAL) so
> looking on S3 which is just the monitoring server.
> Anyone else seen this? Thanks for any help you can give me.
> ClaireB

Log Shipping Error 4323

Hi all,

Scenario : MS Windows 2003 SP1 - MS SQL Server 2000 EE SP4

Log Shipping

My secondary server was restoring backup logs successfully, but suddenly i got the following error:

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4323: [Microsoft][ODBC SQL Server Driver][SQL Server]The database is marked suspect. Transaction logs cannot be restored. Use RESTORE DATABASE to recover the database.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.

How can I fix this situation without re create the maintenance plan ?

We had the same issue some time ago, this was related to a new file being created in a filegroup on the source. Don't know if this applies to your case, but restoring a backup of this new file on the secondary server solved the pb (unless we had to restore the whole db, sorry I can't remember, my memory is gone... together with the customer :o).

HTH,

Vincent

Log Shipping Error 4323

Hi all,

Scenario : MS Windows 2003 SP1 - MS SQL Server 2000 EE SP4

Log Shipping

My secondary server was restoring backup logs successfully, but suddenly i got the following error:

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4323: [Microsoft][ODBC SQL Server Driver][SQL Server]The database is marked suspect. Transaction logs cannot be restored. Use RESTORE DATABASE to recover the database.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.

How can I fix this situation without re create the maintenance plan ?

We had the same issue some time ago, this was related to a new file being created in a filegroup on the source. Don't know if this applies to your case, but restoring a backup of this new file on the secondary server solved the pb (unless we had to restore the whole db, sorry I can't remember, my memory is gone... together with the customer :o).

HTH,

Vincent

Log Shipping does not ship to secondary database

Good day to the people who's reading this post!

I have some trouble with shipping my transaction logs to the secondary database be it on

another server or within the same server to another database instance.

Im using SQL Server 2005 workGroup editions with Service Pack 2.

Here are the problems that i encountered.

I do hope someone has bumped into such a problem and willing to help me out.

I tried on 2 separate servers (not domain environment) and also between 2 separate instances

(which is supposed to be simple!) on

our development server,but was unsuccessful.

Between 2 separate instances on the development server -

No error after configuring Log shipping though.

The configuring went through and it was a success.

Transaction logs was backup every minute and it got copied over to the other folder.

But SQL Agent not doing its last job which is supposed to restore to the secondary database on

another instance.No errors given out in SQL Agent error log files.Anywhere else im supposed to

look to see if there are errors given out?

Both instances SQL Agent has the same log on username and password with Administartive rights

So what went wrong?

Between the 2 servers-

The transaction logs was backup every 1min on the primary server

but it didn't got copied over to to the secondary database.

- Error message given was:Error in restoring database to the secondary

database.Network path given could not be found. Can't open the AxTest.bak file.

(i am very sure i have type the correct network path,even have shared it out and i think the firewall

is blocking incoming traffic since unlike our development server,

which allows us to access when we use Start>Run and type in the ip address,user name

and password,the primary server will only tell me no network path was found)

I also believe it's because the SQL Agent on the secondary database server wasn't given

permission to access the primary database folder.

I've shared out the drive and folder on the secondary server and

even have allowed SQL Agent to read,write and modify on both servers.

For the primary and secondary SQL Agent,

I configure their log on to be the same user account name and password

which have administrative rights.

So what went wrong?

Isit really true that both servers have to be in domain environment before you can configure log

shipping,mirroring and replication?

Hope someone help me out of this predicament.Thank you in advance!

Log shipping between domains or different work groups is pretty picky. Here's a link I used when setting it up before.

http://support.microsoft.com/default.aspx/kb/321247/

This section may apply to your situation:

Cross Domain Log Shipping

If computers that are running SQL Server are placed in a multi-domain environment, Microsoft recommends that you set up two-way trusts between all domains that are involved in log shipping. However, if you cannot establish trusts between domains, you can use network pass-through security for log shipping. Refer to the section of this article that discusses the LocalSystem network account startup option for SQL Server-related services.

Why your restores are not working between instances is another story. Maybe try restoring the copied tran log manually and see if it works or generates any errors. Add some log files to the job steps directly and see if you get anymore information.

Good Luck|||

Hi Mark!Your post is certainly very helpful!

Could you correct me if i misunderstood the article please.

Does it mean that if i set up the SQL services log on account on the secondary database with the exact match of a local user account which exists on the primary

database server,

my secondary database SQL services will be able to access the primary database because it has bypass the network security?

Hope to hear your reply soon.

By the way i found out that certain ports has to be open to enable SQL services to execute the log shipping properly.

Port 135 - for the services

Ports 137, 138, and 139 or 445 need to be open too cuz im using UNC path for the folder sharing

This link will provide more information. Hope it will help others...

http://72.14.235.104/search?q =cache:Nb5a6PvckzwJ:www.stlssug.org/docs/Log-Shipping.ppt+what+port+does+log+shipping+use&hl=en&ct=clnk&cd=1

|||Yes, we synced the administrator passwords on both servers.

Good luck and thanks for the port info.|||

Hi Mark.

Turns out the log shipping still didnt work between the two servers even after opening the ports.

It keeps saying the network path could not be found each time the agent from the secondary database server tried to retrieve the

transaction log file to restore a new database.

Any idea if there is any other security settings that might be blocking the server from accessing the primary database

server network path? Or do you know if i have to enable any services?

My log shipping between the 2 instances work now though.

SQL Browser were disabled just had to enable it back.

Thanks in Advance!

|||If you have already done/tried the things below, I can't help you. There seems to be a network disconnect. Is there a trust set up between the 2 servers? Sorry, I'm not a network admin guy.

Local Network Account

You can use SQL Server to start under a locally-created network account. In the situation where there is network access required by a SQL Server process, which is the case if you have configured SQL Server to use log shipping, you can use network pass-through security. With pass-through security, all machines that will be accessed by SQL Server must have the same network account with the same password and appropriate permissions, configured locally. Additionally, when the SQL Server process requests resources from the second computer, traditional network security is bypassed if the same account (under which the requesting SQL Server service is started) exists with the same password. As long the account on the second computer is configured with enough permission to carry out the task that is requested by calling SQL Server, the task will be successful.

Local System Account

You can also configure SQL Server to start under the Local System account. Modifying the password for the LocalSystem account may result in the failure of some services that are critical for system stability. This account is local to the computer where it resides, which means that the security context that SQL Server services uses is local. As stated in the Local Network Account section, you cannot use network pass-through security when you start SQL Server under the LocalSystem account because the passwords for the LocalSystem account on different computers are different. The starting of SQL Server under this account when network resource access is required will most likely result in the unsuccessful completion of tasks.

Log Shipping does not ship to secondary database

Good day to the people who's reading this post!

I have some trouble with shipping my transaction logs to the secondary database be it on

another server or within the same server to another database instance.

Im using SQL Server 2005 workGroup editions with Service Pack 2.

Here are the problems that i encountered.

I do hope someone has bumped into such a problem and willing to help me out.

I tried on 2 separate servers (not domain environment) and also between 2 separate instances

(which is supposed to be simple!) on

our development server,but was unsuccessful.

Between 2 separate instances on the development server -

No error after configuring Log shipping though.

The configuring went through and it was a success.

Transaction logs was backup every minute and it got copied over to the other folder.

But SQL Agent not doing its last job which is supposed to restore to the secondary database on

another instance.No errors given out in SQL Agent error log files.Anywhere else im supposed to

look to see if there are errors given out?

Both instances SQL Agent has the same log on username and password with Administartive rights

So what went wrong?

Between the 2 servers-

The transaction logs was backup every 1min on the primary server

but it didn't got copied over to to the secondary database.

- Error message given was:Error in restoring database to the secondary

database.Network path given could not be found. Can't open the AxTest.bak file.

(i am very sure i have type the correct network path,even have shared it out and i think the firewall

is blocking incoming traffic since unlike our development server,

which allows us to access when we use Start>Run and type in the ip address,user name

and password,the primary server will only tell me no network path was found)

I also believe it's because the SQL Agent on the secondary database server wasn't given

permission to access the primary database folder.

I've shared out the drive and folder on the secondary server and

even have allowed SQL Agent to read,write and modify on both servers.

For the primary and secondary SQL Agent,

I configure their log on to be the same user account name and password

which have administrative rights.

So what went wrong?

Isit really true that both servers have to be in domain environment before you can configure log

shipping,mirroring and replication?

Hope someone help me out of this predicament.Thank you in advance!

Log shipping between domains or different work groups is pretty picky. Here's a link I used when setting it up before.

http://support.microsoft.com/default.aspx/kb/321247/

This section may apply to your situation:

Cross Domain Log Shipping

If computers that are running SQL Server are placed in a multi-domain environment, Microsoft recommends that you set up two-way trusts between all domains that are involved in log shipping. However, if you cannot establish trusts between domains, you can use network pass-through security for log shipping. Refer to the section of this article that discusses the LocalSystem network account startup option for SQL Server-related services.

Why your restores are not working between instances is another story. Maybe try restoring the copied tran log manually and see if it works or generates any errors. Add some log files to the job steps directly and see if you get anymore information.

Good Luck|||

Hi Mark!Your post is certainly very helpful!

Could you correct me if i misunderstood the article please.

Does it mean that if i set up the SQL services log on account on the secondary database with the exact match of a local user account which exists on the primary

database server,

my secondary database SQL services will be able to access the primary database because it has bypass the network security?

Hope to hear your reply soon.

By the way i found out that certain ports has to be open to enable SQL services to execute the log shipping properly.

Port 135 - for the services

Ports 137, 138, and 139 or 445 need to be open too cuz im using UNC path for the folder sharing

This link will provide more information. Hope it will help others...

http://72.14.235.104/search?q =cache:Nb5a6PvckzwJ:www.stlssug.org/docs/Log-Shipping.ppt+what+port+does+log+shipping+use&hl=en&ct=clnk&cd=1

|||Yes, we synced the administrator passwords on both servers.

Good luck and thanks for the port info.|||

Hi Mark.

Turns out the log shipping still didnt work between the two servers even after opening the ports.

It keeps saying the network path could not be found each time the agent from the secondary database server tried to retrieve the

transaction log file to restore a new database.

Any idea if there is any other security settings that might be blocking the server from accessing the primary database

server network path? Or do you know if i have to enable any services?

My log shipping between the 2 instances work now though.

SQL Browser were disabled just had to enable it back.

Thanks in Advance!

|||If you have already done/tried the things below, I can't help you. There seems to be a network disconnect. Is there a trust set up between the 2 servers? Sorry, I'm not a network admin guy.

Local Network Account

You can use SQL Server to start under a locally-created network account. In the situation where there is network access required by a SQL Server process, which is the case if you have configured SQL Server to use log shipping, you can use network pass-through security. With pass-through security, all machines that will be accessed by SQL Server must have the same network account with the same password and appropriate permissions, configured locally. Additionally, when the SQL Server process requests resources from the second computer, traditional network security is bypassed if the same account (under which the requesting SQL Server service is started) exists with the same password. As long the account on the second computer is configured with enough permission to carry out the task that is requested by calling SQL Server, the task will be successful.

Local System Account

You can also configure SQL Server to start under the Local System account. Modifying the password for the LocalSystem account may result in the failure of some services that are critical for system stability. This account is local to the computer where it resides, which means that the security context that SQL Server services uses is local. As stated in the Local Network Account section, you cannot use network pass-through security when you start SQL Server under the LocalSystem account because the passwords for the LocalSystem account on different computers are different. The starting of SQL Server under this account when network resource access is required will most likely result in the unsuccessful completion of tasks.