Log Shipping – Error: Failed to update database “DATABASE NAME” because the database is read-only

Here’s a strange one that I’ve recently come across.  I had a customer report that their log shipping restore jobs were chock a block of errors.  Now, the logs seem to have been restoring just fine but before every restore attempt, the job is reporting the error,

Error: Failed to update database “DATABASE NAME” because the database is read-only.

Unfortunately I haven’t got any direct access to the server but their logshipping is setup to disconnect users before and leave the database in standby after.  After a bit of to-ing and fro-ing, I asked the customer to send me a trace file covering the period that the restore job ran.

Looking at the trace, it was fairly easy to see where the error was occuring…

ALTER DATABASE [SQLUndercover] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Failed to update database “SQLUndercover” because the database is read-only.

ALTER DATABASE statement failed.

So the problem is with switching the database to single user mode, the reason that it’s doing this is in order to disconnect all users.  But why should this be a problem?  There shouldn’t be an issue with changing a read only database to single user, I’ve got log shipping jobs that do it all the time.

Starting to doubt myself, I even tested it out on a local instance and all behaved as I’d expected it to.  So what’s going on?

Well it turns out that there’s a bug in SQL Server 2012 and 2014 that will prevent you from changing a read only database to single user mode.

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.