I am setting up Transaction Log shipping in Standby/ Read-Only mode of two databases to multiple instances.
After backing up and restoring the transaction logs to their separate instances (one copy of each DB on each instance), one of the databases has no issue when I:
ALTER DATABASE [dbname] SET SINGLE USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [dbname] SET MULTI_USER;
the other one throws an error:
Failed to update database "other_dbname" because the database is read-only.
I cannot see any difference between the two. I've checked properties and all of the options are the same. From the options tab, the database that would throw the error had the Compatibility level set to SQL Server 2012 and one that would allow me to ALTER had that value set to SQL Server 2014, but changing them both to 2014 did not resolve the issue.
I'm at a complete loss as to what the issue could be or what I could look into that would point me in the correct direction.
edit: the above commands are executed on a linked server, if that could have anything to do with it. The users are the same, the permissions are the same, I'm able to execute the commands on the first database with no issue whether they are executed through SSMS or through a job and they always fail on the second database.