June 13, 2008 at 2:34 am
Hello,
I'm having some problems with Log Shipping.
I've made it work normally. The problem happens after the morning Online Backup is done and I get the following error on the Secondary Server Restore.
The log shipping secondary database secondaryserverip\database has restore threshold of 45 minutes and is out of sync. No restore was performed for 235 minutes. Restored latency is 30 minutes. Check agent log and logshipping monitor information.
I get one of this messages as well from Restore Monitor:
Message
2008-06-13 08:45:15.78*** Error: Could not apply log backup file '\\share\folder\database.trn' to secondary database 'database'.(Microsoft.SqlServer.Management.LogShipping) ***
2008-06-13 08:45:15.78*** Error: This log cannot be restored because a gap in the log chain was created. Use more recent data backups to bridge the gap.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
2008-06-13 08:45:15.81*** Error: The log backup file '\\share\folder\database_20080613041516.trn' was verified but could not be applied to secondary database 'database'.(Microsoft.SqlServer.Management.LogShipping) ***
2008-06-13 08:45:15.81Deleting old log backup files. Primary Database: database'
2008-06-13 08:45:15.85The restore operation completed with errors. Secondary ID: '1e301821-7ce3-4a90-b10f-77298df75240'.
I'm thinking in suspending Log Shipping while the Online Backup is doing its job. Is there a way to supend LS for an hour or so?
Thanks in advance.
June 13, 2008 at 5:23 am
Hi,
What type of backup are you performing in the morning?
The error would appear to suggest that the sequence of log files is being interrupted. This will occur if you perform a transaction log backup outside of the log shipping configuration (i.e. via a maintenance plan).
June 13, 2008 at 6:35 am
Hello,
it's an Online Backup during the week and one Offline Backup made on Sundays.
I suspect that the Backup process interferes with LS by doing a Truncate on the Logs when the Online Backup is proceeding.
Any ideas to surpass this?
Thank you.
June 13, 2008 at 6:48 am
Hi,
I am a little confused as I was under the impression that all SQL Server Backups were online, so perhaps another poster can clarify this point?
My question was looking at whether a FULL, Differential, File or transaction log backup was being performed as part of your backup schedule?
Performing a FULL database backup for example does not truncate the transaction log.
June 13, 2008 at 7:26 am
Hello,
I'm talking on Server Backups, not SQL.
So, when doing a full Online Server Backup something happens that messes around with LS and it starts getting an Out of Sync message from then on.
Thank you.
June 14, 2008 at 1:37 pm
I did a test to check if the error would happen again.
Placed LS doing its normal job until a certain time. After an hour it would resume LS again.
Still have the Out of Sync error.
I'm getting out of ideas for this situation.
Help! 🙂
June 14, 2008 at 3:24 pm
is the server backup using a SQL agent that backs up the SQL database. This could cause a gap in the log chain if it was a log backup. Check with whoever controls the server backup.
The term 'online' backup could suggest it is doing a backup of the sql database files even though they are in use.
Only a log truncate or log backup outside of logshipping jobs could cause this error.
---------------------------------------------------------------------
June 14, 2008 at 4:06 pm
Thanks George, that was my idea as well.
Do you have any recommendations in the way to avoid the Truncate of the DB?
Thank you in advance.
June 15, 2008 at 4:35 am
jjssilva (6/14/2008)
Thanks George, that was my idea as well.Do you have any recommendations in the way to avoid the Truncate of the DB?
Thank you in advance.
well, presuming you are using some server backup software such as veritas or sabre it will have a SQL 'agent' component, and within that part of the GUI, it must be configured to truncate the logs somewhere, or do a log backup, this option needs to be turned off.
You need to consider whether you want the server backup to include 'hot' (or online) backups of your databases, it all depends on your DR strategy, and the process you plan to use to recover the server. Will you rebuild the system databases and restore from backups using SQL utilities or rely on the server backups. Check on how the tool recovers the databases, chances are you still have to restore the databases and do some sort of system database recovery but from the server software backup tool GUI.
Whatever method you use I would always still have backups on disk, It keeps db recovery under DBA control and enables fastest recovery of individual databases from disk.
A good trick is whenever you upgrade SQL, and the instance is down anyway, copy your system db files off to another directory. Then these files will get backed up by the server backup and restored in A DR situation. You can then move them back to their proper location, the instance will restart and you can bring the databases up to date from your backups
---------------------------------------------------------------------
June 16, 2008 at 1:30 am
Thank you once again George.
I'll check if our backup software is using SQL agent.
Nevertheless I did another test that might prove that the problem isn't the backup procedure but something else.
I've scheduled LS to stop everyday for one hour (normal hour in the afternoon when nothing's happening besides normal work). It still gave the Out of Sync error after resuming LS automatically.
Will test it again today, but I still don't understand what might be causing it.
Best Regards.
June 16, 2008 at 1:46 am
check how long you are keeping the tran log backups for on the primary. All I can think of if you get an out of synch error after not restoring for an hour is the tran log backup file has been deleted from the primary
---------------------------------------------------------------------
June 16, 2008 at 1:59 am
Hi,
The line from you error message included below would seem to suggest that the log files are being located successfully but that they are unable to applied to the database.
2008-06-13 08:45:15.81 *** Error: The log backup file '\\share\folder\database_20080613041516.trn' was verified but could not be applied to secondary database 'database'.(Microsoft.SqlServer.Management.LogShipping) ***
Are you able to confirm what your restore settings are? I am wondering if there are users connected to the database and the configuration is not disconnecting the users in order to perform the restore operation and is therefore failing.
The checkbox option I am referring to can be found on the "Restore Transaction Log" tab of the wizard and is labeled "Disconnect users in the database when restoring backups"
Also, any further troubleshooting you carry out, if you could also provide details of any error messages, error log details that would be most appreciated.
Cheers,
John
June 16, 2008 at 2:37 am
Hello again,
I've been using No Recovery Mode on the Secondary Transaction Log.
Do you recomend placing it on Standby Mode with the Disconnect users in the database when restoring backups activated?
I might be wrong, but won't this disconnect the users on the production server?
June 16, 2008 at 2:59 am
The point from the previous post is worth checking out but the original error message does contain the line :
2008-06-13 08:45:15.78 *** Error: This log cannot be restored because a gap in the log chain was created. Use more recent data backups to bridge the gap.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
suggesting there are transactions missing.
also, thinking about it, jjsilva, I note your out of synch' threshold is set at 45 mins, so if you do not do a restore for an hour you will get these Messages, so its not really an error just a warning you can ignore with regard to the test u did in the afternoon
---------------------------------------------------------------------
June 16, 2008 at 3:07 am
Hi,
I would not recommend that you change the restore mode of your configuration as if you are using the with No Recover option the database will not even be accessible to users.
You can test the theory that George has outlined by manually running the various SQL Server jobs that control your log shipping configuration.
For example, run the Alert threshold job to see if it generates the error messages you are detailing. The job is typically called LSAlert_ServerName by default.
Next Manually run the copy job (LScopy_ServerName) followed by the restore job (LSRestore_ServerName).
If you now run the Alert threshold job again, and no error messages are presented then it is indeed a timing issue.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply