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 1234»»»

Log Shipping Secondary Server Restore problem Expand / Collapse
Author
Message
Posted Friday, June 13, 2008 2:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 4:07 AM
Points: 43, Visits: 203
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.
Post #516475
Posted Friday, June 13, 2008 5:23 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, August 23, 2014 6:03 AM
Points: 351, Visits: 1,556
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
Post #516568
Posted Friday, June 13, 2008 6:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 4:07 AM
Points: 43, Visits: 203
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.
Post #516617
Posted Friday, June 13, 2008 6:48 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, August 23, 2014 6:03 AM
Points: 351, Visits: 1,556
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
Post #516629
Posted Friday, June 13, 2008 7:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 4:07 AM
Points: 43, Visits: 203
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.
Post #516665
Posted Saturday, June 14, 2008 1:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 4:07 AM
Points: 43, Visits: 203
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! :)
Post #517195
Posted Saturday, June 14, 2008 3:24 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:12 AM
Points: 5,863, Visits: 12,942
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.


---------------------------------------------------------------------

Post #517199
Posted Saturday, June 14, 2008 4:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 4:07 AM
Points: 43, Visits: 203
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.
Post #517200
Posted Sunday, June 15, 2008 4:35 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:12 AM
Points: 5,863, Visits: 12,942
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


---------------------------------------------------------------------

Post #517244
Posted Monday, June 16, 2008 1:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 4:07 AM
Points: 43, Visits: 203
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.
Post #517378
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse