March 31, 2016 at 11:50 am
While investigating a repot of a broken log-shipping job, I discovered that the database was in (Standby), NOT (Standby / Read Only) mode. Sure enough, sys.databases shows is_read_only = 0 and is_in_standby = 1.
What does this mean? Is there a legitimate use for a DB configured this way?
I'm guessing the way to get Log shipping working would be a backup & Restore with norecovery, assuming a TRN file with the next LSN is available, and if not, a restore (norecovery) from the Log shipping primary database?
March 31, 2016 at 4:52 pm
If the database is in standby, AFAIK, it's read only. No transactions can be run here.
March 31, 2016 at 5:16 pm
Normally, in SMMS the database icon is gray, and is labeled (Standby \ Read Only). This time the Icon was yellow and labeled simply (Standby).
The log shipping job which applies TRN files to it had been failing, and I was unable to run RESTORE ... WITH RECOVERY, BACKUP or set it offline. All of these produces errors saying that the undo file was in use be another process.
A colleague of mine restarted the SQL Service and detached the database, then I restored a backup from the primary server (with norecovery) and log shipping resumed.
One idea we have is that an error occurred during the last TRN restore in which the WITH STANDBY did not complete the Standby \ Read-Only step properly. I did not find this scenario described in my google searches.
March 31, 2016 at 9:33 pm
Weird, if you do something in the db, maybe add a table, does it fail?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply