February 19, 2016 at 1:56 pm
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?
Thanks,
lw
February 23, 2016 at 10:34 am
Find the last restored file from https://msdn.microsoft.com/en-us/library/ms188004.aspx
Then manually restore the chain of LOG backups up to the most recent one. You can also configure log shipping to automatically restore the list of log backups. If you have everything configured correctly you should really just be able to re-run the restore job.
The real question is why did it fail, and is there something more going on like a missing LOG backup file. If that's the case you simply copy the missing backup over and re-run the restore job.
P.S.
The
You can also configure log shipping to automatically restore the list of log backups.
setting was really bugging me because I couldn't remember where it was, and it's not in the front end dialog box. It's here in case you were wondering: select restore_all from log_shipping_secondary_databases
...and you can configure it here: sp_change_log_shipping_secondary_database
February 23, 2016 at 12:56 pm
Hi Jon.Morisi,
I restored the full backup and from the table [log_shipping_monitor_secondary] find the last restored file is: '\\DRserver\...folders\UserInfo_1_20160222091505.trn'. I don't know why it restored to the date/time of 02/22/2016 at 4:15:00 AM. Should I manually restore the log to the most recent one like the last log backup file at 10 minutes ago 2/23/2016 at 2:45:00 PM?
Thanks.
lw
February 23, 2016 at 1:52 pm
Yes, since you restored a FULL backup, you now need to restore every LOG backup that has occurred since the FULL.
February 23, 2016 at 3:00 pm
Hi Jon.Morisi,
My production DB is over 100 GB and the DR server is in different state. So, if I make a full backup and copy the (.bak file) over to the DR server, it will take hours to finish. During these hours, should I suspend or disable the log restore processing in the secondary server or it doesn't matter?
Is there a way to restore all the log files (maybe 20+ of them from the full backup to the current time) by using SSMS in one time?
Thanks,
lw
February 23, 2016 at 3:17 pm
I thought you already did that:
Hi Jon.Morisi,
I restored the full backup
If you haven't, try just re-running the log shipping restore job and checking the logs to see what the problem is.
February 23, 2016 at 3:44 pm
Hi,
After I manually restored the full backup, I enabled the log restore in the secondary server to start the log restoring (every 15 minutes as designed). I got error every time:
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.70Searching 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 (the time of UserInfo_1_20160222093006.trn created up).
Following this step in the restoring, the processing skipped all the .trn files and delete all old .trn files over 3 days(as designed). The last last step is with this message:
The restore operation completed with errors. Secondary ID: '69da3a6f-4921-4556-bf1e-3af6807d407d'
Do you think restore all the log files from full backup will fix the error?
Thanks,
lw
February 23, 2016 at 4:00 pm
The log shipping restore job is failing because,
UserInfo_1_20160222093006.trn' is too recent to apply to the secondary database
You need to restore the LOG backups that are older than UserInfo_1_20160222093006.trn. Specifically,
since you restored a FULL backup, you now need to restore every LOG backup that has occurred since the FULL.
February 24, 2016 at 5:08 am
please don't double post, see my reply here
http://www.sqlservercentral.com/Forums/Topic1763576-1549-1.aspx?Update=1
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 24, 2016 at 2:25 pm
Hi Jon.Morisi,
After restoring a full backup and multiple log backups, the log shipping jobs resume to run.
Thank you so much for the helps.
Best regards,
lw
February 24, 2016 at 2:42 pm
Hi Perry Whittle,
I didn't want to double post the topic. As I found the other area has a more meaningful description about my topic, I was trying to transfer this post over there. But, after I copy over the post, I couldn't delete the old one.
This post has many valuable and knowledgeable answers. I'd like to keep this post and delete the other one. Please tell me the way to delete the post if there is any.
Thanks,
lw
February 25, 2016 at 11:46 am
Firstly check the state of the primary database and report back on that.
also check the results of this query run against the primary and secondary
select name, differential_base_lsn, differential_base_time from sys.master_files
where database_id = db_id('your sql log ship db') and type_desc = 'ROWS'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 25, 2016 at 12:49 pm
Hi,
Even I restored the log files and resume the LS, there is an issue when I restore the T-log. As I need to restore many log files and after click OK to restore, I got a "DB in use error" each time:
Restore failed for Server 'UserInfo_1'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&LinkId=20476
I found the spid by using this query:
select spid from master..sysprocesses where dbid = db_id('UserInfo_1') and spid <> @@spid
and use kill '##' to disconnect the database with users.
Is there a way to disconnect the DB from all users during the restoring? Or can I use the script to restore the log with some code in there to force the DB disconnected from any user?
Thanks,
lw
February 27, 2016 at 11:54 am
check sysprocesses and hard line is kill the users spid.
Otherwise call them and ask them to rescind their connection\session
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 27, 2016 at 2:47 pm
Put it in single user mode
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply