Log Shipping 'skipping' all logs and not restoring

  • I am trying to configure Log Shipping between a production and tests Server. The configuration of Log Shipping seemed to work successfully,

    3 SQL Server Agent jobs have been created on the Secondary Server.

    LSAlert

    LSCopy

    LSRestore

    1 SQL Server Agent job has been created on the Primary Server

    LSBackup

    The jobs are all running as per my intended schedule. Logs are being created and shipped (appearing in the intended directory of the secondary server).

    Before setting up Log Shipping I did a manual restore of a full backup of the database from Prod to Test with 'standby' option, and then during LogShipping Config chose the option 'No Secondary Database is Initialised'

    The database in Test is restored and in Standby/ReadOnly Mode

    The problem occurs when the LSRestore Job runs. It reads all the available Log files, but chooses to ignore them all and returns the following messages...

    Skipped Log Backup File .....

    Could not find a log backup file that could be applied to secondary Database

    The job finishes OK, but nothing is restored. After a few hours of this the Monitor job starts to complain that the Secondary Database is out of Sync, but I believe this to be a symptom rather than the cause.

    Any help greatly appreciated

    Neil

  • respected sir,

    i want to transfer the database after a fixed interval time on to the another server how can i do it.

    and that transfer database is automatically restore on to the another server.

    and from the another server i want to see the reports

    please help me.

  • Neil,

    Pls check if the Sql service account in secondary server has sufficient permissions to the shared folder in which the log backup is being copied to inorder to perform restoration...check the job history in secondary server to find the exact error message and post it here..the out of sync error appears if the log backup is not restored in the secondary for a specified amount of time..

    [font="Verdana"]- Deepak[/font]

  • Pravin, your scenario can be accomplished by log shipping in SQL 2000 and SQL 2005. You can refer these links for more info,

    http://sql-articles.com/articles/lship/lship.htm

    [font="Verdana"]- Deepak[/font]

  • respected sir,

    i need all steps of the logshipping i want to transfer the data one server to another server in sql server 2005 and transfered data wants to automatically restore on another server and what are jobs i have to created and how it's created and from another server i want to see the Reports. please if possible give me all detailed steps.i am very thankful to you for giving me response.

  • Pravin,

    All the required information is available in the link i gave above...you have the following topics listed in the link I provided earlier.,

    Topics

    *****

    What is Log Shipping?

    Pre-requisites for Log Shipping

    10 Steps to configure Log Shipping

    Fail-Over in Log Shipping

    Role Change in Log Shipping

    [font="Verdana"]- Deepak[/font]

  • Hi it may be that the account used by you sql server agent is a local account rather than a domain account and/or does not have enough privilages to connect to or update the monitor server

    But you the following check list might help a little

    Make sure that the SQL Server Agent service is running for the secondary server instance and that the restore job for this secondary database is enabled and is scheduled to run at the appropriate frequency.

    The restore job on the secondary server might be failing. In this case, check the job history for the restore job to look for the cause.

    The log shipping restore job, which runs on the secondary server instance, might not be able to connect to the monitor server instance to update the log_shipping_monitor_secondary table. This might be caused by an authentication problem between the monitor server instance and the secondary server instance.

    The backup alert threshold might have an incorrect value. Ideally, this value is set to at least three times the frequency of the restore job. If you change the frequency of the restore job after log shipping is configured and functional, you must update the value of the backup alert threshold accordingly.

    When the monitor server instance goes offline and then comes back online, the log_shipping_monitor_secondary table is not updated with the current values before the alert message job runs. Error 14421 can be raised when a restore job succeeds with, "Could not find a log backup file that could be applied to secondary database." When this occurs, the restore time is not updated. The cause of the error in this case might be an issue with the copy job.

    To update the monitor tables with the latest data for the secondary database, run sp_refresh_log_shipping_monitor on the secondary server instance.

    On the secondary or monitor server instance, the date or time is incorrect. This may also generate alert messages. Possibly the system date or time was modified on the one of them.

    CodeMinkey

  • Try restoring the logs manually to the standby server and see what, if any, error is received.

    In the past while debugging, I have found alerts set up on production systems to monitor transaction log growth. When the log is 80% full, the alert truncates the log. Of course, this was done by an an inexperienced DBA attempting to stop "transaction log full" error messages.

    Obviously, this would affect log shipping.

  • Janet, Keith, Deepak,

    Thank-you for all your shared experience and advice. I tried a different tack, and worked. My Primary Database had alkready been configured for Transaction Log Backups. The backups were produced with naming convention dbname_backup_timestamp.trn. When I configured log shipping, a 2nd set of transaction log files was produced with namimg convention dbname_timestamp.trn. These 2nd files were then shipped successfully, but I believe the log sequencing with the files was disrupted by the initial transaction log backups.

    So...

    - I stopped transaction log backups on the primary database

    - De-configured log shipping (is that a word ???? 🙂 )

    - took a new FULL backup of the primary database

    - restored this to the 2ndary database (with standby option)

    - deleted ALL .trn file on primary and 2ndary servers

    - Reconfigured log shipping

    the restore job now picks up logs and reports that it has restored them.

    Regards, Neil

    ps. I think I have another problem with availability of the 2ndary database now. It shows up in SQL Management Studio as (restoring...) and the database cannot be opened. I hope this resolves itself as the restore 'catches up'.

    - configured log shipping

  • Hi Neil,

    This will most likely be due to the recovery option you have selected when you reconfigured log shipping. You need your log shipping setup in Standby mode which will leave the secondary database online, but read only.

    So maybe you want to re-initialise log shipping again, but this time choose the Standby mode.

    There is a better explanation here:

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

    Hope That Helps - Nathan

  • Aaah Nathan, thankyou. If only I could find these replies before spending the day working it out for myself (OK, with Google then..) Yes, you are right, I will have to re-initialise the log-shipping process again, do another backup and restore, and re-start. This time I think it WILL work as I desire.

  • Hi,

    I have this problem also. All the jobs execute successfully, but the restore job checks each transaction log and skips all of them. I can manually load the first valid one, and the next ... and then after the next backup, copy restore cycle... the restore job just carries on skipping all the logs.

    Anyone know - how is it storing the LSN it's looking for?

    Cheers,

    R

  • I was noticing the same thing, and what I discovered was that Primary was backing up and shipping to secondary just fine, and restores were being attempted. However, since there were zero changes, everything was being skipped. I forced a change on Primary, and it found the correct file and restored. No error, and alerts went away.

    The bummer of all this is that it's not smart enough to log the attempt. So if you have a small database with zero changes say for 2 hours, and you set your alert for 45 minutes, it's going to scream at you often even though there is no issue.

  • I have same issue.

    Where did you forced the restore option or which changes you made on primary server??

    Thanks in advance.. 🙂

  • Jinu43 (2/10/2012)


    I have same issue.

    Where did you forced the restore option or which changes you made on primary server??

    Thanks in advance.. 🙂

    You can do the 1st restore log after full backup in SSMS manually

    and see if it fixes now.

    Regards,

    Skybvi

    Regards
    Sushant Kumar
    MCTS,MCP

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply