Menu
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?
895
To verify the current state of a database, select the state_desc column in the sys. databases catalog view or the Status property in the DATABASEPROPERTYEX function.
After moving your SQL Server, the Properties may update to be Read Only. This can result in the Exception similar to "Failed to update database "Acctivate" because the database is read-only". You can update the SQL Database permissions in Microsoft SQL Server Management Studios (SSMS).
Is there any way to calculate TPS (transaction per second) of SQL Server database? You can use the "Batch Requests/sec." PMON counter (under the SQL Statistics category) for an overall measure of database requests processed per second.
T-SQL identifiers, meanwhile, are used in all databases, servers, and database objects in SQL Server. These include the following tables, constraints, stored procedures, views, columns and data types.
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. 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'); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
