SQL Server Database Status and monitoring sometimes return false negatives. The monitoring should reflect planned Database Status changes – such as ETL processes  and  SQL Server Restores , consider  adding a parameter to include the Database Status. Let me explain.

Currently , the monitoring is returning (example)  this CRITICAL message:

COMP_MS_SQL_DB_Status_Crit INST1:SERVER1:MSS Fri Jun 03 19:31:44 2011

 When the database is in RESTORING mode. Under the criteria of : Available or Not Available – that’s correct . But if you take into account , ETL and other planned RESTORES – then this can cause reporting to be inaccurate

From a monitoring perspective –  Operations raise issues in Helpdesk – which are not necessary.

 SQL Server database states are:

 ONLINE – Database available for access

OFFLINE – Database is unavailable

RESTORING – A file is being restored

RECOVERY PENDING – A resource related error during recovery

SUSPECT – Primary file group is suspect . Possibility of damage

EMERGENCY – Administrator explicitly changed the database to Emergency

 To view the current Database State , there are  two methods :

 Method 1 - Use the “state_desc” column on sys.databases

Method 2 – DATABASEPROPERTYEX and status

To avoid returning false negatives – and causing unnecessary reporting the formula to return relevant the Database status  , should  include a parameter such as  :

 Database Status is IN (‘OFFLINE’,’RECOVERY_PENDING’,’SUSPECT’,’EMERGENCY’)  

 Therefore avoiding reporting on  SQL server RESTORES  that are  planned.

 Read More

SQL Server – read only database status

MSDB Suspect pages table and bad - SQL Server DBA

SQL Server Database SUSPECT state - SQL Server DBA