SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server Database Status,monitoring and Restores

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 :

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

Sys.databases state_desc 

Method 2 – DATABASEPROPERTYEX and status

DATABASEPROPERTYEX 

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


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

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer