July 20, 2010 at 12:47 pm
Hi,
I set up a log shipping from one of my databases to a secondary one. The process seems working, but then the secondary database is out of sync. I couldn't find over the internet any way to know what was the last .trn restored on my Secondary Database. Or a way to sync the databases again. I re configure the log shipping again. after the first restore the problem happens again. (not sync between databases)... I repeat this process 3 times and I don't find a way to know why is not working.
i don't have "error" on the logs. My log say that it couldn't restore xx file:
Message
2010-07-20 14:17:26.93Skipped log backup file. Secondary DB: '[database]', File: 'E:\LogShipping\VwDb_20100720181709.trn'
2010-07-20 14:17:26.93Could not find a log backup file that could be applied to secondary database [database].
2010-07-20 14:17:26.93The restore operation was successful. Secondary Database: [database], Number of log backup files restored: 0
Message
2010-07-20 14:17:26.94Deleting old log backup files. Primary Database: [database]
2010-07-20 14:17:26.94The restore operation was successful. Secondary ID: 'a63adfac-ba29-4275-ae99-3ecf5d35636e'
Message
2010-07-20 14:17:26.96----- END OF TRANSACTION LOG RESTORE -----
my log shipping runs every hour (for each event) and I get this kind of message.
My primary Server has a Replication set up, but it replicates to another database. I'm not sure if this can cause a conflict for the log shipping.
Is anybody has an experience with this kind of issues? what is the best practice?
Thank you very much in advance 🙂
July 20, 2010 at 1:00 pm
Actually, this is the complete log summary for the last restore:
Message
Microsoft (R) SQL Server Log Shipping Agent
[Assembly Version = 9.0.242.0, File Version = 9.00.1399.00]
c Microsoft Corp. All rights reserved.
2010-07-20 14:45:00.22----- START OF TRANSACTION LOG RESTORE -----
Message
2010-07-20 14:45:00.28Starting transaction log restore. Secondary ID: 'a63adfac-ba29-4275-ae99-3ecf5d35636e'
2010-07-20 14:45:00.28Retrieving restore settings. Secondary ID: 'a63adfac-ba29-4275-ae99-3ecf5d35636e'
2010-07-20 14:45:00.30Retrieved common restore settings. Primary Server: '10.122.60.31', Primary Database: '[Database]', Backup Destination Directory: 'E:\LogShipping', File Retention Period: 129600 minute(s)
2010-07-20 14:45:00.30Retrieved database restore settings. Secondary Database: '[Database]', Restore Delay: 0, Restore All: True, Restore Mode: Standby, Disconnect Users: True, Last Restored File: <None>, Block Size: Not Specified, Buffer Count: Not Specified, Max Transfer Size: Not Specified
2010-07-20 14:45:00.35Searching through log backup files for first log backup to restore. Secondary DB: '[Database]'
Message
2010-07-20 14:45:00.52Skipped log backup file. Secondary DB: '[Database]'
, File: 'E:\LogShipping\VwDb_20100720161501.trn'
Message
2010-07-20 14:45:00.69Skipped log backup file. Secondary DB: '[Database]', File: 'E:\LogShipping\VwDb_20100720171501.trn'
Message
2010-07-20 14:45:00.85Skipped log backup file. Secondary DB: '[Database]', File: 'E:\LogShipping\VwDb_20100720181500.trn'
Message
2010-07-20 14:45:01.03Skipped log backup file. Secondary DB: '[Database]', File: 'E:\LogShipping\VwDb_20100720181709.trn'
2010-07-20 14:45:01.03Could not find a log backup file that could be applied to secondary database '[Database]'.
2010-07-20 14:45:01.05The restore operation was successful. Secondary Database: '[Database]', Number of log backup files restored: 0
2010-07-20 14:45:01.05Deleting old log backup files. Primary Database: '[Database]'
2010-07-20 14:45:01.05The restore operation was successful. Secondary ID: 'a63adfac-ba29-4275-ae99-3ecf5d35636e'
2010-07-20 14:45:01.05----- END OF TRANSACTION LOG RESTORE -----
Exit Status: 0 (Success)
Message
Executed as user: domain\user. The step succeeded.
I dont understand why it doesnt take the backups. I have the TUF file.
July 20, 2010 at 1:12 pm
Error says that the secondary database is skipping the transaction logs as it already applied them.
You should check the copy, restore and alert timings of the database..maybe they are not correct.
There should be .tuf file in the folder where your files are restored on sec server
tuf files indicate that the ther transaction logs are actually restoring and will not be out of sync..
see this
http://support.microsoft.com/kb/329133
it says
You may have set an incorrect value for the Out of Sync Alert threshold. Ideally, you must set this value to at least three times the frequency of the slower of the Copy and Restore jobs. If the frequency of the Copy or Restore jobs is modified after log shipping is set up and functional, you must modify the value of the Out of Sync Alert threshold accordingly
check out if ther is latency between copy and restore jobs..
are your copy and restore jobs working fine? if yes, then its probably the time settings.
Regards
Sushant Kumar
MCTS,MCP
July 20, 2010 at 1:28 pm
Error says that the secondary database is skipping the transaction logs as it already applied them.
You should check the copy, restore and alert timings of the database..maybe they are not correct.
There should be .tuf file in the folder where your files are restored on sec server
tuf files indicate that the ther transaction logs are actually restoring and will not be out of sync..
see this
http://support.microsoft.com/kb/329133
it says
You may have set an incorrect value for the Out of Sync Alert threshold. Ideally, you must set this value to at least three times the frequency of the slower of the Copy and Restore jobs. If the frequency of the Copy or Restore jobs is modified after log shipping is set up and functional, you must modify the value of the Out of Sync Alert threshold accordingly
check out if ther is latency between copy and restore jobs..
are your copy and restore jobs working fine? if yes, then its probably the time settings.
I changed the alerts to notify 90mintues later if not copy or restore hasnt done.
I backup every hour, then after 15min I copy and 15 mintues later I restore.
I do have thee .TUf file in my folder, and the .trn are been copied with no problem. I dont understand why the restore skip the files. I can see the database is not in sync because I added a table in primary server and it doesnt show in the secondary server.
ideas?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply