3

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

  • Read your update. You have a little more going on here than first appeared. Updating my answer. – Adam May 10 '19 at 14:01
  • Can you expand on the link-server facet? You aren't running the ALTER DATABASE commands on the instance with the databases. You're doing it on an instance linked to the instance with the subject database? – Adam May 10 '19 at 14:20
3

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.

| improve this answer | |
  • Both of them are MULTI_USER – RIanGillis May 9 '19 at 20:27
  • You're awesome. After applying the service packs everything runs smoothly. – RIanGillis May 10 '19 at 21:04
0

STANDBY/read_only allows a database to be brought up for read-only access between transaction log restores, and it can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores. You cannot apply Alter commands on the standby database. Think of the purpose of commands that you are executing on the database.

ALTER DATABASE [dbname] SET SINGLE USER WITH ROLLBACK IMMEDIATE;

This command used when number of sessions are connected and you need to apply operation at database level and let the database put in single user(your current session) and rollback all ongoing/pending transactions. In your case database is already in standby case which contains committed trasnactions and uncommitted transactions are added in TUF file.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.