System objects could not be updated in database 'x' because it is read-only.

Question

Post reply

System objects could not be updated in database 'x' because it is read-only.

KGERBR

Say Hey Kid

Points: 689
More actions
July 27, 2015 at 1:17 pm

#306190

Hi,
We installed SP1 for SQL Server 2014 this past weekend and got this error message in the logs.
I found that if you set the db to read-write, it updates the system objects, even after SP1 has completed. Then you can set it back to read-only. I'm just posting this so other people can find it on the internet, as I wasn't able to find it specifically.
Error Log Entry:
System objects could not be updated in database 'x' because it is read-only.
Problem:
After installing SP1 for SQL Server 2014 you will find this message in the error logs saying read-only databases could not be updated.
Solution:
Simply set the db to read-write and the system objects will get updated, long after SP1 was installed.
ALTER DATABASE [x] SET READ_WRITE WITH NO_WAIT
Then set it back to read-only:
ALTER DATABASE [x] SET READ_ONLY WITH NO_WAIT
You should then see these log entries:
System objects could not be updated in database 'x' because it is read-only.
Setting database option READ_WRITE to ON for database 'x'.
Starting up database 'x'.
CHECKDB for database 'x' finished without errors on 2015-07-25 01:02:28.143 (local time). This is an informational message only; no user action is required.
Synchronize Database 'x' (129) with Resource Database.
Setting database option READ_ONLY to ON for database 'x'.
Starting up database 'x'.
CHECKDB for database 'x' finished without errors on 2015-07-25 01:02:29.888 (local time). This is an informational message only; no user action is required.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

le.kinou Valued Member Points: 74 More actions · Answer 1

Hello,

If we do not switches back to READ_WRITE in order to perform system objects updates

=> Is there a risk ?

Thanks

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

le.kinou (9/30/2016)
Hello,
If we do not switches back to READ_WRITE in order to perform system objects updates
=> Is there a risk ?
Thanks

I've not yet run into this problem but I'm pretty sure that the database won't actually come online unless you cycle it like KGERBR said.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

le.kinou Valued Member Points: 74 More actions · Answer 3

Hello,

I've just upgraded my test server

FROM SQL Server 2014 SP2 TO SQL Server 2014 SP2-CU1 (KB3178925) - 12.0.5511.0 (X64).

- 27 databases are Read/Write

- 18 databases are ReadOnly

Walkthrough :

> SET OFFLINE all my databases (where database_id > 4) (in order to follow precisely databases ONLINE step)

> Apply SP2-CU1

> SET ONLINE all my OFFLINE databases.

The result is very strange.

All my databases are ONLINE.

But, only one readOnly database sent me the following message after set online statement :

"System objects could not be updated in database 'xxxxx' because it is read-only."

=> This database is the only one that was created before the SP2 was applied.

In our production environnement, we have 1722 ReadOnly databases.

So if we have to execute :

> SET READ_WRITE

> SET READ_ONLY

> backup

It will be very IO and time consumming to backup all our changed databases.

Hence my question : Is there a risk ?

Thanks.

Laurent.