SQL Server – Handling Two Databases in Standby/Read-Only Mode

log-shippingsql server

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.

screen shot showing Standby/ Read-Only and MULTI_USER

options:
screen shot of options

Best Answer

Microsoft disclosed an issue in 2015 related to SQL Server 2012 and 2014 that sounds very similar to your problem:

Assume that you set a Microsoft SQL Server 2012 or SQL Server 2014 database to read-only mode. When you then try to set it to single-user mode, you may receive the following error message:

"Msg 3906, Level 16, State 1, Line 51 Failed to update database "SDP" because the database is read-only.

Msg 5069, Level 16, State 1, Line 51 ALTER DATABASE statement failed."

You can read about the full issue and the fix in FIX: You cannot set a SQL Server 2012 or 2014 database to single-user mode if the database is read-only.

It looks like you're running SQL Server 2014 SP1 with a security update (from 08/2017) applied. The referenced issue would impact that version of SQL Server, and I believe the fix is in 2014 SP1 CU 2

I'm running SQL Server 2017. In that version, I was able to successfully put a database in single-user then read-only (case-1) and read-only then single-user (case-2):

/**CASE-1**/
--Setting single user then read-only
ALTER DATABASE [foo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [foo] SET READ_ONLY;

--Setting single user then read-only
ALTER DATABASE [foo] SET READ_WRITE;
ALTER DATABASE [foo] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

/**CASE-2**/
--Setting single user then read-only
ALTER DATABASE [foo] SET READ_ONLY;
ALTER DATABASE [foo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

--Setting single user then read-only
ALTER DATABASE [foo] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [foo] SET READ_WRITE;

I checked the documentation for SQL Server 2014, and it looks like this functionality works the same as in 2017 (at least there is no noted difference). So, I take back my earlier assertion that you can't place a read-only database in single-user mode.

Related Question