Click here to monitor SSC
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 sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
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

---------------------------------------------------------------------
John.Sansom
John.Sansom
SSC-Addicted
SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)

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


John Sansom (@sqlBrit) | www.johnsansom.com
George Silva
George Silva
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 205
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?
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
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

---------------------------------------------------------------------
John.Sansom
John.Sansom
SSC-Addicted
SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)

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


John Sansom (@sqlBrit) | www.johnsansom.com
George Silva
George Silva
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 205
Strangely It failed again at 6:45AM. (Online Backup starts at 5:00AM).
Will try to get some more info and will get back to you.

Appreciate your patience.
John.Sansom
John.Sansom
SSC-Addicted
SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)

Group: General Forum Members
Points: 433 Visits: 1558
Yes please do.


John Sansom (@sqlBrit) | www.johnsansom.com
George Silva
George Silva
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 205
Hello,

just confirmed the error. It started going Out of Sync at 6:15AM and got the first Event Error at 7:00AM.
So it really seems to be something with the Online Backup.
Strangely I've checked the Backup software and the scripts are not configured to do Log Cleaning.

I already did the Schedule test to stop before Backup and restart Backup, the same errors came out.

Any more ideas? Is there any extra info you need?

Thanks once again.
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 13687
if a log backup is happening, or a truncate log, it will be recorded in your errorlog. go through that to see if you can find evidence of a log backup outside of your logshipping plan.

---------------------------------------------------------------------
George Silva
George Silva
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 205
No errors or Truncate on the Logs.
Tomorrow I will have a final report from the Backup Team, but so far I can't seem to find a logical reason behind this Out of Sync on the LSRestore SQL Agent.
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