Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

Log shipping restore keep restore the same file

Hi,
do anyone know why the log shipping restore job keep replay the same file again and again. from the log shipping restore history I dont see any failure been capture for a restoration process.
Furthermore I'm checking the physical Tlog backup and found that after the earlier tlog backup has been restored and  there was a backup file *.bak in between and then only another Tlog here. the thing that make me headache is bcause nothing error found in my restore log shipping histiry and when I click at restore job it succeeded but only keep restore the previous file
0
motioneye
Asked:
motioneye
  • 3
  • 2
1 Solution
 
lahousdenCommented:
A few questions:

1) Is Log Shipping still taking new transaction log backups and copying them to the destination server?
2) Is there actually any problem with the restored database on the destination server?
3) What is the "Maximum job history log size (rows)" on the Job System tab on the SQL Server Agent Properties window on the destination server?
4) Are there updates that have happened to the main database that happened before the last transaction log was shipped that you know have not been applied to the database on the destination server?

That should do for starters...
0
 
motioneyeAuthor Commented:
1) Is Log Shipping still taking new transaction log backups and copying them to the destination server?
Yes
2) Is there actually any problem with the restored database on the destination server?
Of course there is a problem now
3) What is the "Maximum job history log size (rows)" on the Job System tab on the SQL Server Agent Properties window on the destination server?
Not to sure but I guarantee that I ca see it keep roll the same Tlog file since I can see the same *.TRN been replay when I manually start the restore job
4) Are there updates that have happened to the main database that happened before the last transaction log was shipped that you know have not been applied to the database on the destination server?
No so far no any... The last file before the main db took the full db backup was the Tlog backup that been restored successfully only after the full db backup and this Tlog cannot be apply
0
 
lahousdenCommented:
Thanks.  A few more questions and some follow-up:

a) Do you have Log-Shipping configured so that you have read-access to the restored database on the destination server (e.g. for reporting, , etc.)?
b) Are you running SQL 2000 or SQL 2005?
c) How long has this log-shipping set-up been running uninterrupted (i.e. since you last had to stop Log-Shipping, remove it and re-configure it)?
0
 
motioneyeAuthor Commented:
a) No
b) SQL2000 as I dont choose sql2005 as my topic
c) This running ok just suddenly I have this issue, the Log shipping has been implemented for 3 mth and it's worked fine
0
 
lahousdenCommented:
<<Not to sure but I guarantee that I ca see it keep roll the same Tlog file since I can see the same *.TRN been replay when I manually start the restore job>> - in our Log-Shipping setup I have not been able to find out where to look to see what *.trn file Log-Shipping is trying to restore...  where can you look to find this out?

<<This running ok just suddenly I have this issue, the Log shipping has been implemented for 3 mth and it's worked fine.>> - Three months sounds like you have had a good run so far...  Log Shipping was a new introduction for the SQL 2000 release so there are some wrinkles that were still being worked out.  I can't think of anything sensible to suggest to straighten your current situation out as it is.  What I would do now (and have done with our Log-Shipping set-up when it stops behaving as expected - which isn't often) would be to remove and re-configure the Log-Shipping set-up.  This would be the path with the best expectation of a successful outcome.
To remove log-shipping, remove Log-Shipping from the Maintenance Plan on your source server, then delete the maintenance plan on the source server.  Then delete the Maintenance Plan with the same name on the destination server and delete the replicated database on the destination server (it may take SQL Server a long time [e.g. 20 minutes or so] to do this if you leave "delete backup and restore history" checked - don't worry: I guess it's because there isn't a suitable index on the history tables).  Then you should be able to set-up Log-Shipping as you did in the first place.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now