December 27, 2016 at 10:35 am
Hello,
i am using SQL 2008 R2, doing log shipping, everything is working good for all jobs, backup logs, copy log from primary to secondary, BUT, the restore one does... work? what i mean by that is, i run the restore log shipping job, but doesn't apply the transaction logs that have been copied over, i see all the transaction logs shipped and looks good, i checked permissions and seeing that account has full access, actually its the owner of the folder, and i run the job, it runs successfully, but in the monitoring screen/report, it is blank of on the part that says "Last Backup file" and "Last Restore File" but on the "Last copied file" it shows the latest transaction file that was copied over, but never seems to apply the transaction file... what am I doing wrong? also the retention is about 270 minutes, but the restore delay is 90 minutes, and backup restore and copy jobs are 15 min backup, copy is 20, and restore is 25...
thanks in advanced
December 27, 2016 at 2:06 pm
Hello Everyone,
hope this error message helps to narrow down what might be wrong, sadly it doesnt help me, i am not sure what to do 🙁
Message
2016-12-27 13:00:02.10*** Error: The log backup file '\\secondaryServer\appdb\APPDB_20161227191501.trn' was verified but could not be applied to secondary database 'APPDB'.(Microsoft.SqlServer.Management.LogShipping) ***
2016-12-27 13:00:02.10Deleting old log backup files. Primary Database: 'APPDB'
2016-12-27 13:00:02.11The restore operation completed with errors. Secondary ID: 'a11d279e-d22b-435a-8fda-b3a6ede09ea6'
2016-12-27 13:00:02.12----- END OF TRANSACTION LOG RESTORE ----
why couldnt the transaction log not be applied to the secondary, and is it deleting an old transaction log backup files already? i set the cleanup to delete 36 hours... weird.
December 27, 2016 at 2:16 pm
Check for any log backup jobs that happened outside of the log backup restore jobs that you expect to run. From SSMS you can right click the database name, select reports, standard, then Backup and Restore events run on the primary. You may be able to resume log shipping if you can find and apply that log.
December 27, 2016 at 2:35 pm
Hey Richard,
thanks for the info, i will make sure transaction log backups are disabled on primary, i dont think there is any on the secondary, but i will double check... i will let you know the outcome.
thanks 🙂
December 27, 2016 at 3:00 pm
I would agree that it seems that a log backup somewhere broke the log backup chain.
I think you can query log_shipping_secondary_databases to find out the last file name and date of the file that was last restored. If the log backup chain has been broken, it would be after that file name, file date. You could query the backup tables in msdb...at a high level something along the lines of this to get everything:
select *
from dbo.backupfile bf
inner join dbo.backupset bs
on bf.backup_set_id = bs.backup_set_id
inner join dbo.backupmediafamily bmf
on bs.media_set_id = bmf.media_set_id
where database_name like 'YourDatabase'
order by backup_finish_date desc
Then you can see what backups ocurred since the last restored file and file date from log_shipping_secondary_databasesand maybe narrow things down and see what you can do to address it.
Sue
January 2, 2017 at 1:56 am
Are you running log shipping as Standby on the secondary? If so, maybe check to see if there is a connection on the secondary that isn't being disconnected. This issue is discussed here:
January 3, 2017 at 4:29 pm
Thank you everyone for your input, turns out there was another transaction log backup being done... guess as they say, to many hands in the cookie jar?
after i turned that off, re-did EVERYTHING for log shipping, now its a champ, working. Big thanks to you all but bigger thanks to richard.bowles 😀
February 23, 2017 at 3:55 am
Siten0308 - Tuesday, January 3, 2017 4:29 PMThank you everyone for your input, turns out there was another transaction log backup being done... guess as they say, to many hands in the cookie jar?after i turned that off, re-did EVERYTHING for log shipping, now its a champ, working. Big thanks to you all but bigger thanks to richard.bowles 😀
For future reference, log shipping can be reinitialised using a differential backup as long as a full backup on the primary has not occurred since the log shipping plan suffered the missing log backup file. Querying the differential base lsns on the primary and secondary will indicate if a differential restore is possible
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply