I need to know how to interrogate a Microsoft SQL Server, to see if a given database has been set to Read-Only or not.
Is that possible, using T-SQL?
Add a comment
|
6 Answers
Querying sys.databases for checking a DB's Read-Only property will only give the right information if the database has been explicitly set to Read-Only mode.
For databases that are in the passive servers (e.g. in AlwaysOn technology Secondary Servers), even though the databases cannot be written into, their Read-Only mode in sys.databases would still be set as False(0).
Hence, it is advisable to check the Read-Only mode of databases using the statement:
SELECT DATABASEPROPERTYEX('MyDBNAme', 'Updateability');
3 Comments
Tim Friesen
This worked for me as well to check for Primary vs Secondary on an Azure Database. Of note, the values returned are READ_ONLY and READ_WRITE. Also, in Azure, if you query from a database that does not match the database name passed into the first parameter, the result will be NULL.
Tim Friesen
So this works well on Azure: SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');
AjV Jsy
Useful to add the server name to the script, for when you're trying it on several servers
SELECT @@SERVERNAME AS ServerName, DATABASEPROPERTYEX('(databasename)', 'Updateability') AS Status;The information is stored in sys.databases.
SELECT name, is_read_only
FROM sys.databases
WHERE name = 'MyDBNAme'
GO
--returns 1 in is_read_only when database is set to read-only mode.
Comments
I was trying to use the p.campbell's answer to check if my Azure SQL DB is the primary one or the read only replica - it didn't work. Both the primary DB and the replica returned had 0 on the is_read_only field.
Here's what worked for me:
SELECT DATABASEPROPERTYEX('MyDBNAme', 'Updateability');
the above select statement returns string 'READ_ONLY' or 'READ_WRITE'.
Comments
If DB is part of your Always On and the secondary node is designed in Read_Only then "sys.databases --> Is_Read_Only" column wont show the correct result ! its a bug that Microsoft needs to address it during the next versions.
Comments
If you would like to check all DB statuses in your server, use this:
SELECT name, user_access_desc, is_read_only, state_desc, recovery_model_desc
FROM sys.databases;
You can quickly determine your next steps.
