question

chitrarekha.saha avatar image
chitrarekha.saha asked

Error only on the primary server

Configured log shipping using the following procedure http://www.mssqltips.com/sqlservertip/2301/step-by-step-sql-server-log-shipping/. I have enabled logshipping for 2 databases. The wizard created LSAlert job. Error: Executed as user: NT AUTHORITY\NETWORK SERVICE. The log shipping primary database macinename.AbraHRMS_Live has backup threshold of 60 minutes and has not performed a backup log operation for 78 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14420). The step failed. However the LSBackup job for the database has no error. This is for only primary server database where I see error. The secondary server has no error in the view history and Transaction Log Shipping status. Why do I get error in the primary server? For both databases, I have enabled log shipping and I backup at the same time @12 AM, copy after 15 mints @12:15 AM and restore after 15 mints @ 12:30 AM. Is this right?
log-shipping
6 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
Had the LSBackup job actually run with the previous 78 minutes? If this job doesn't run, it won't show an error of its own. If the log was not backed up on the primary server, that is the only place the error would show up - log backups are not taken on the secondary server.
0 Likes 0 ·
chitrarekha.saha avatar image chitrarekha.saha commented ·
Backup, copy and restore is working without any errors in logshipping and the transactional log status report also does not have any errors in the secondary server. However the primary server shows alert message, and it is showing wrong/old backup file. It is not writing the recent backup file. So some where the table is not getting updated. Since the alert is mentioning about the secondary server, I would think that the issue is with the secondary server. So how do I update the secondary server and which table needs to be updated. In the primary server dbo.logshipping_primary_databases is showing old backup date.
0 Likes 0 ·
KenJ avatar image KenJ commented ·
So, the backup job ran successfully within the last 78 minutes, but didn't record the file correctly for the log shipping monitor? I din't see the reference to the secondary machine in the alter/error message, so didn't suspect the secondary server as the problem.
0 Likes 0 ·
chitrarekha.saha avatar image chitrarekha.saha commented ·
Yes, that is right seems like a simple problem but I am having hard time fixing this issue.
0 Likes 0 ·
KenJ avatar image KenJ commented ·
If you look at the job history in SQL Agent, does it confirm that the log shipping backup job is running? If it really runs it has to create a backup file and update the file name in the log shipping monitor.
0 Likes 0 ·
Show more comments

1 Answer

·
chitrarekha.saha avatar image
chitrarekha.saha answered
If the SQL Server service account on your primary server runs under the local system account, you must create your backup folder on the primary server and specify a local path to that folder. I think the issue was caused by the sqlserver service account. The service is running as Network Service. I created the backup folder in the primary server and the copy folder in the secondary server and all is well. The transaction Log shipping status is without any errors
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
good deal. Be sure to accept and/or vote for your answer so people know this was the solution.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.