July 27, 2015 at 1:17 pm
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.
September 30, 2016 at 1:43 am
Hello,
If we do not switches back to READ_WRITE in order to perform system objects updates
=> Is there a risk ?
Thanks
October 5, 2016 at 7:02 pm
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
Change is inevitable... Change for the better is not.
October 10, 2016 at 8:20 am
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.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy