February 23, 2016 at 10:32 am
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? Also, how can I reset the state from Single_User to Multi_User? as I can't even open the properties of the DB.
Thanks,
lw
February 24, 2016 at 5:03 am
lw1990 (2/23/2016)
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? Also, how can I reset the state from Single_User to Multi_User? as I can't even open the properties of the DB.
Thanks,
lw
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 5:52 am
Have you ticked the box in the log-shipping restore screen that says Disconnect users in the database when restoring backups?
If you miss that, you sometimes get this problem.
February 25, 2016 at 12:23 pm
Hi Beatrix Kiddo,
Yes I did check that check box.
Thanks,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply