Hi,
I have a log shipping process set for a long time and the restore step is failed on the secondary server while the backup step from the primary DB and log copying job are still running.
As the restore is failed, the status of the secondary DB is set to (Standby/Single User/Read-Only) mode. The message in the LOG File Viewer shows in 3 main steps are (didn't copy all error messages here):
-Disconnecting users. Secondary DB: 'UserInfo_1'
-Error: Exclusive access could not be obtained because the database is in use.
-'UserInfo_1.trn' was verified but could not be applied to secondary database
-The restore operation completed with errors. Secondary ID: 'f33c970b-c36c-4c5c-8f51-dcbcf2ef3205'
Basically, as the restore step was failed and set the database UserInfo_1 to Single User mode and the next restore can not be finished. Also the log files in the secondary server destination file location were kept for more than 10 days even I set them to be deleted after 3 days.
Please give me some idea on how I can resume the restore step. Should I manually restore a full backup and a log backup that right after the full backup? Or is there other way to let the restore restart in the log shipping chain?
Hi,
Normally you can restore a latest full backup on top of it (with norecovery), it should be able to resume the log shipping process on its own. It might be better to take a new full backup and apply it just to speed up the log recovery process.
Hope this helps.
Thank you Dennisc,
It looks like I'll make a full backup and restore it to the secondary DB. But, before I restore it, I have to change the state of this DB as it is in single user mode. I can't even open the properties of the DB to change the state to MULTI_USER. Here is the error MSG when I was trying to open the properties:
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Database 'UserInfo_1' is already open and can only have one user at a time. (Microsoft SQL Server, Error: 924)
Many thanks to the replies,
Now, I've restored a full backup and found from the table in msdb [log_shipping_monitor_secondary] the last restored file is: '\DRserver...folders\UserInfo_1_20160222091505.trn' which is 02/22/2016 at 4:15:00 AM. (I don't know why it restored this log file). But, when the log shipping runs again, I got error:
Message
2016-02-23 15:30:10.69 *** Error: The file > '\\DRserver\...folders\UserInfo_1_20160222093006.trn' is too recent to apply to the secondary database 'UserInfo_1'.(Microsoft.SqlServer.Management.LogShipping) ***
2016-02-23 15:30:10.69 *** Error: The log in this backup set begins at LSN 400155000000151000001, which is too recent to apply to the database. An earlier log backup that includes LSN 400155000000149000001 can be restored.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
2016-02-23 15:30:10.70 Searching for an older log backup file. Secondary Database: 'UserInfo_1'
Does it mean the log restore processing can NOT find the log backup files in the secondary server? But, the folder of the files restore from has all last 3 days' log backup files -- they were shipped from the primary DB server in every 15 minutes. There are many log files before and after 2/22/2016, 4:30 AM.
Did I miss anything after the manual full backup restoring?