SQL Server – read only database status

This post describes how to return the database read-only status via T-SQL .

The system view – sys.databases  returns a column called is_read_only.  

The column returns either 1 or 0

 1 = read only

0 = read write

   SELECT [name] ,is_read_only  
   FROM sys.databases  
   ORDER BY [name]

 

See Also

SQL Server Database Status,monitoring and Restores

SQL Database Status with sys.databases

Hungarian Notation is misunderstood

Hungarian notation refers to a identifier naming convention.  The variable indicates the variables use – as opposed to what they are.  Hungarian notation is often applied incorrectly.

Hungarian notation usage is characterised by variables using a group of lower case letters followed by a name given by the DBA\Developer\Programmer. Hungarian notation was widely used in the programming with C on MS Windows.

Examples in programming

liFlightPath    (li = long integer)

cAbbreviation (c=character)

I see a  misinterpretation of Hungarian Notation slip into SQL Server via either

a) programmers applying naming conventions, they would normally use in their programming environment

b) Migrating a data structure from another system

 

Examples of Hungarian Notation misuse in SQL Server

tblSalesData

vwCustomers

spAddress

fnConvertTime

 

These prefix examples are attempting to describe by what they are. Not the intended usage.

I don’t see the point of adding tbl, sp,fn,idx etc , as that all they can be.  If an object appears after EXEC , it won’t be a table , it will be a procedure.

 Working for different organisations , means dealing with different naming standards – if they have any in the first place.  The main aspect of creating a naming convention is to standardise object name and coding procedures.

See Also

SQL Server Database names with spaces

ISO-11179 Naming Conventions and SQL DDL

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL SERVER DISABLE INDEX

Use the script below to disable an index on a SQL Server table

ALTER INDEX MY_INDEX_NAME on MY_TABLE_NAME  DISABLE; 

 

Executing the code will  prevent access to the index. 

If it’s a CLUSTERED INDEX , the data remains intact on the table , but no DML can access the data. Drop the index or rebuild to enable  DML .  Disabling the CLUSTERED INDEX will also disable all NONCLSTERED INDEXES referenced to the table.

If it’s a NONCLUSTERED INDEX, the data is physically deleted.  The index definition  details are retained in the meta data.

 Why disable an index?

1)       Strategy for large imports of data .  A typical strategy is disable NONCLUSTERED INDEXES , import the data and  rebuild the NONCLUSTERED INDEXES

2)       Index troubleshooting

3)       Disk constraints in  Index Rebuild. Less disk space is required to commit an Index Rebuild

Related Posts:

Disable Index and Rebuild Index for Updates on very large tables

ALTER INDEX permissions

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Database Status with sys.databases

I was creating a Powershell script for a SQL Server checkout procedure after a reboot , and used  the state_desc column in sys.databases view to report on the database status.

The descriptions , courtesy of BOL are:

ONLINE  - Database is available for access

RESTORING – One or more files are being restored

RECOVERING – Database is being recovered

RECOVERY_PENDING  - SQL Server has encountered a resource-related error during recovery

SUSPECT  - At least the primary filegroup is database suspect and may be damaged

EMERGENCY - User has changed the database and set the status to EMERGENCY

OFFLINE – Database is unavailable

SELECT name, state_desc as Database_status
FROM sys.databases

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Stored Procedure Checklist

DBAs find it easier to manage large Database Server environments when checklists are used. This Stored procedure checklist  will stimulate some ideas you could use

For acceptance into the SQL Server environment , I’ve developed some scripts that scan the T-SQL code for some items in this checklist

       1)  Same case for T-SQL Keywords. I prefer UPPER CASE , but I know other DBA\Developers like lower case.

      2)  Inspect input parameters for valid values (numbers, strings etc)

      3)  Force developers to justify  usage of  dynamic SQL (SP_EXECUTESQL, EXEC('SELECT col1…..')

      4)  Commenting. I prefer a comment block at the top with usage and revision history . Snippets throughout the code to enhance the understanding

      5)  Limit xp_cmdshell usage

      6)  Think in set theory. Avoid looping  such as cursors.

      7)  Clean up . Close objects ,drop #temp tables, deallocate\close cursors, close transactions

      8)  Use try...catch blocks. Trap errors and report into a log file

     9)  Performance is satisfactory

    10) Investigate input parameters for SQL injection attacks. Better if it occurs at the application level .

     11)  Manage  security. Follow the SQL Server Security Policy.Access to stored procedure and underlying objects

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)