12 June,2011 by Jack Vamvas
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 :
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.