SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log Shipping Secondary Server Restore problem


Log Shipping Secondary Server Restore problem

Author
Message
George Silva
George Silva
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 205
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.81 Deleting old log backup files. Primary Database: database'
2008-06-13 08:45:15.85 The 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.
John.Sansom
John.Sansom
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1799 Visits: 1558
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).


John Sansom (@sqlBrit) | www.johnsansom.com
George Silva
George Silva
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 205
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.
John.Sansom
John.Sansom
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1799 Visits: 1558
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.


John Sansom (@sqlBrit) | www.johnsansom.com
George Silva
George Silva
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 205
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.
George Silva
George Silva
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 205
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! Smile
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24256 Visits: 13698
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.

---------------------------------------------------------------------
George Silva
George Silva
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 205
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.
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24256 Visits: 13698
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

---------------------------------------------------------------------
George Silva
George Silva
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 205
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search