Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Log Shipping 'skipping' all logs and not restoring Expand / Collapse
Author
Message
Posted Thursday, January 3, 2008 3:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 18, 2009 11:23 PM
Points: 9, Visits: 29
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
Post #438664
Posted Saturday, January 5, 2008 11:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 4, 2011 10:58 PM
Points: 6, Visits: 28
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.


Post #439316
Posted Sunday, January 6, 2008 8:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 26, 2012 2:14 PM
Points: 402, Visits: 598
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..


- Deepak
Post #439340
Posted Sunday, January 6, 2008 9:02 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 26, 2012 2:14 PM
Points: 402, Visits: 598
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


- Deepak
Post #439341
Posted Sunday, January 6, 2008 10:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 4, 2011 10:58 PM
Points: 6, Visits: 28
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.
Post #439428
Posted Sunday, January 6, 2008 11:00 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 26, 2012 2:14 PM
Points: 402, Visits: 598
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


- Deepak
Post #439437
Posted Monday, January 7, 2008 2:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:32 AM
Points: 77, Visits: 254
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.



Kevin Brennan M.Sc, MCITP, MCDBA, MCSE, MCTS,MCP+I
Post #439481
Posted Monday, January 7, 2008 8:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2008 11:53 AM
Points: 46, Visits: 85
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.




Post #439617
Posted Monday, January 7, 2008 8:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 18, 2009 11:23 PM
Points: 9, Visits: 29
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
Post #439850
Posted Monday, January 7, 2008 10:49 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, October 6, 2014 5:34 PM
Points: 633, Visits: 369
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
Post #439874
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse