36

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?

Oreo
  • 529
  • 3
  • 16
Giuseppe
  • 625
  • 1
  • 9
  • 15

6 Answers6

44

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.
p.campbell
  • 98,673
  • 67
  • 256
  • 322
43

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');
Oreo
  • 529
  • 3
  • 16
Masood Hashim
  • 531
  • 4
  • 7
  • 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 Sep 19 '17 at 20:08
  • 3
    So this works well on Azure: SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability'); – Tim Friesen Sep 19 '17 at 20:14
  • 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;` – AjV Jsy Mar 06 '19 at 09:47
8

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'.

Rafal Zajac
  • 1,613
  • 1
  • 16
  • 13
6

Here is a command to display or set this property. 

EXEC sp_dboption "AdventureWorks", "read only"

Sample output 

OptionName CurrentSetting    
read only OFF
JohnFx
  • 34,542
  • 18
  • 104
  • 162
0

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.

Mehrdad Alemi
  • 119
  • 1
  • 10
0

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.

Ethan
  • 876
  • 8
  • 18
  • 34
farhad hossain
  • 1
  • 1