Log Shipping Not replicating on Secondary Server

Question

Hi All

It's my first attempt for log shipping. i have restore the backup of Primary Database(Production db) to the development server in Standby/Read only mode.

I have checked error logs and job activity monitor but there are no errors but the changes doesn't seems to be replicated on Secondary database.

Production server and development server are two different mechines.

firstly im running LSRestore Job then after 15 minutes LSCopy job. I checked shared network folder which has .trn (log files), which is fine as well

My question is - why changes i cannot see the changes on Secondary server. if i am making any changes on to the Primary DB?

Is there anything I am missing?

please guide me

Thanks in advance 

Regards

TM

Answer 1

LS Restore job doesn't throw any errors if there are logs to apply.

Normal order of the jobs would be : LSBackup job on the Production Server, then LSCopy job, then LSRestore Job.

Take a look at the last 2 or 3 steps of the last jobrun of the LSRestore Job. You can find some information there. Another Problem could be, that there aren't any log backups you can apply on your secondary depending on the point in time you've taken the full backup of your primary database. If the backup is too old, means, you don't have a complete log-backup chain beginning with the endtime of your full backup, the LSRestore job won't be able to apply any logs, but you should find this information in the LSRestore Jobhistory.

cheers,

Answer 2

Hi Wagner

Thanks for your prompt response

I just ran the LSBack job first and it created Log file in shared folder then LSCopy and at the end LSRetore job it ran successfully all of them ran successfully.

However when checked the job log of LSRestore it said could not find a log backup file that could be applied to seconday database.

does it mean anything?

Regards

TM

Answer 3

Hello

anybody can help me?

Thanks

Answer 4

You may be missing a transaction log file or the secondary instance service account may not have access to the share where the TRN files live

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 5

hey,

from when is your full database backup, and what's your retention time of your transaction log backup ( you can see that with a rightclick on your database -> tasks -> ship transaction logs -> backup settings)?

as stated before normally this error occurs when you have a broken backup chain means your full backup is too old, and you don't have all transaction log backups you need.

Can you confirm that lscopy is copying the transactionlog backup files on your local secondary server?

Answer 6

you can check same from RestoreHistory table under MSDB tosee  if your logs are getting restored or not [log backup type is L]

Answer 7

Check your security settings to ensure that the jobs are running under an account which has access to the file share where the backups on the secondary are located, and make sure the LSN chain hasn't been disrupted by having backups occur outside the log-shipping plan.

If those are all correct, I know of one other situation which happened to me, and it was because there were no new transactions to restore from the t-logs since the initial restore of log-shipping.  If you are able to manually restore, but getting the "no pages restored" message, than this is most likely, especially as you have said you set-up log-shipping on a test server where there probably aren't any active transactions occurring often.  Once I initiated a transaction on the database being log-shipped, and waited for the t-log to be generated and copied over to the secondary server, I noticed that my Restore jobs suddenly started working from that point onward.  I don't know what the root cause is, but I suspect that if there is nothing further to restore within the t-logs since the last restore, they get skipped.  Of course, if there aren't any new transactions, then all of them would be skipped and you would get the message "Could not find a log backup file that could be applied to secondary database" as a result.

Hope this helps!

---

Diane

Answer 8

both the SQL Services must be started with a same account name , check the image

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 9

Hi,

Pls check out the LSN of Production server db and Secondary server db....they wont be in  sequence.

Nxt verfy tht copy job is executing or not in there are lof file been copied in shared folder of secondary server.

---

Regards, Ashish ----------------------------------------------------------- Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker..

Answer 10

Just an update, it happened to me again as well but this time in a production environment where I knew there was no activity on a particular database since the last restore.  This time I ran UPDATE STATISTICS dbo.my_table on a random small table in the problem db on the primary server just to get a write to the log file, and presto!  The database restored upon the t-log being generated and copied over to the secondary server, and showed as being fully synchronized from that point on.

Of course this could be ANY transaction that causes a write to the log.  I just chose the most innocuous one I could think of.

I had started another thread on this weird behaviour here, but so far, I haven't got an answer about it.  At least now I have a work-around.

---

Regards,

Diane Sithoo
Database Administrator