asked on

Log Shipping Error

I have set up log shipping for 2 servers. I have the files being sent to a 3rd server and then being copied to the secondary server. The LS jobs on the primary server are being completed and i see the files on the 3rd server. On the secondary server both the LScopy and LSretore are failing. Both are coming back with just Step failed errors and no other reasons. On the monitor i see that it has a 14421 error which i have looked and forums have been saying its not really an error to worry about.

TAB8

The LScopy and LSrestore wont work on the second server as it will be looking for the files localy .. you have them residing on the third server... what i would do is set up log shipping between you pimary and secondary servers and alter the copy job so it copies the log files to the secondard and your third server ..

rbasilides

ASKER

is there no way to have it with a third server. reason being is that the primary server is being mirrored with a secondary server and there is a witness. these server are in another geographic location from us. we are setting up the log shipping to restore on a local SQL. To minimize bandwidth usage on the primary server with want it to quickly transfer the data to the witness server and then pull it from there to our local server.

rbasilides

ASKER

I have set up the LS without the third server now. The LScopy works now but the LSrestore is still failing. This is the only error in the logs that I can find:

Date            6/14/2010 12:08:00 PM
Log            Job History (LSAlert_REQ-SQL-1\REQSQL1)

Step ID            1
Server            REQ-SQL-1\REQSQL1
Job Name            LSAlert_REQ-SQL-1\REQSQL1
Step Name            Log shipping alert job step.
Duration            00:00:00
Sql Severity            16
Sql Message ID            14421
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted            0

Message
Executed as user: REQUIRE\privacy. The log shipping secondary database REQ-SQL-1\REQSQL1.testlogshipping has restore threshold of 45 minutes and is out of sync. No restore was performed for 685043 minutes. Restored latency is 30 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421). The step failed.

rbasilides

ASKER

Update: After reviewing the logs again and looking at the successful steps, on of the actually contained an error that was access issue. found out that one of the sqlagent service was running under another account. went through all accounts to make sure they are the same even on third machine. Now i have the log shipping working with three machines.

New problem is that i create a new table on the primary but on the secondary it is not being added even though all the jobs are completing successfully.

TAB8

Now that is wierd .. is the data being loaded into the secondary .. ie add 100 rows into the primary , do they load into the secondary ?

rbasilides

ASKER

so i figured out that the LSrestore job is completing but it actually is not restoring because it is unable to locate an appropriate file to restore. all the files are there from the backup of the primary. when i try to do a manual restore it says that the file is not the right and that there is an earlier version.

i have tried deleting all the files and removing the log shipping then creating it again and it still does it.

ASKER CERTIFIED SOLUTION

TAB8

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rbasilides

ASKER

So i have it working now. the issue was that there was another backup job running that was saving files that the LSrestore would be looking for. Disabled that job and all is working fine now. created new tables from primary and they shipped over to secondary. It is working well with the third server.

James Murrell

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.