April 12, 2016 at 3:32 am
I have a test suite result DB that a few users run some heavy reporting queries against which conflict with the actual creation of test suite run results so I figured I would configure a read only version using log shipping and thus offload the reporting to that DB. Did this with T-sql many years ago in earlier version but am now running 2014. So I have used the wizard to set everything up, smooth process however I get one error all the time and another a bit intermittently.
To briefly explain the configuration; the secondary DB is on the same host machine and SQL Server instance as the primary and I setup the restore with STANDBY mode and to disconnect users when restoring. At first I didn't have a copy job and tried to backup/restore from the same directory. I ran into the same issues I keep having, even after separating the backup/restore folders.
As for the schedules of backup/copy/restore they initially ran the same minute on a 15 minute interval. The file retention is the default (120 minutes).
The first issue I get all the time is the following message in the job log:
Message
2016-04-12 07:55:01.12*** Error: An error occurred restoring the database access mode.(Microsoft.SqlServer.Management.LogShipping) ***
2016-04-12 07:55:01.12*** Error: Alter failed for Database 'FrostingReadonly'. (Microsoft.SqlServer.Smo) ***
2016-04-12 07:55:01.12*** Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo) ***
2016-04-12 07:55:01.12*** Error: Failed to update database "FrostingReadonly" because the database is read-only.
The above is always in the log, even if the actual restore step succeeds :w00t:.
What is the logshipping .exe trying to alter? I do believe I caught it in Profiler and its trying to set it to SINGLE USER mode. I was also suspecting that the reporting application used was constantly refreshing connection to the DB, I did get a higher success rate when I added a T-SQL step prior to restore that just KILLed every session to the secondary DB.
Some mornings I am greeting by many failed restore jobs and this is additionally in the log:
2016-04-12 07:55:01.14*** Error: The file 'E:\Logshipping\restorefrom\Frosting_20160411223000.trn' is too recent to apply to the secondary database 'FrostingReadonly'.(Microsoft.SqlServer.Management.LogShipping) ***
2016-04-12 07:55:01.14*** Error: The log in this backup set begins at LSN 1335000000245100001, which is too recent to apply to the database. An earlier log backup that includes LSN 1206000012058200001 can be restored.
So I understand that I have an broken sequence and there is a tlog that hasn't been restored but I don't know what causes it. I am suspecting the tlog file was actually deleted before it was restored? I would be surprised if this is the case, I mean the "delete" of tlogs should be based not only on age of file but also the LSN of the tlog file and the secondary database. If it hasn't been restored then don't delete it. I assumed this would be the functionality.
Is this a common issue that can arise for certain job schedule/file retention/restore delay configurations?
Any help would be greatly appreciated!
Timo
April 12, 2016 at 7:25 am
please post the results of this query
select db_name(database_id), differential_base_lsn
from sys.master_files
where database_id IN (db_id('PrimaryDB'), db_id('SecondaryDB'))
and type_desc = 'ROWS'
group by db_name(database_id), differential_base_lsn
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 12, 2016 at 7:54 am
Don't think this is very telling, I was forced to reinitialize the secondary DB from a full backup and currently its all green (still its reporting the error about database access mode in the job output).
Frosting 1344000003483900199
FrostingReadonly1344000003483900199
So its working now but I suspect tomorrow it will have failed too. Also, the default for file deletion is 72 hours apparently, I had changed it to 120 minutes, don't know if that's what caused it.
Appreciate your help,
Timo
April 12, 2016 at 8:22 am
yes, it's no use now.
For future reference, if you have a broken LS plan and the query produces results with identical LSN bases this means that no full backup on the primary has occurred since the plan broke. In this case you can simply take a differential backup on the primary and restore to the secondary, the LS should then resume without issue.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 12, 2016 at 8:44 am
Thanks for the tip, I was expecting there was a remedy similar to this.
I'm just concerned why I should need it in the first place, why has the chain been broken? Do you know if the delete of .trn files are simply by age or if the logshipping program actually checks if it has been restored or not? If it doesn't then maybe its the schedules I should tweak so a delete of an unrestored .trn file can't happen (or atleast minimize the risk).
Any clue as to why I get a failed to change database access mode log output?
/Timo
April 12, 2016 at 8:52 am
there is a retention period defined for the location where the primary writes backups and also defined for where the secondary copies the files too.
On the Primary
exec sp_help_log_shipping_primary_database @database = 'primarydatabase'
On the Secondary
exec sp_help_log_shipping_secondary_database @secondary_database = 'secondary_database'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply