Database status
Job details
| Name: | Database status | |
| Platform: | Sqlserver | |
| Category: | Availability | |
| Description: | Checks if all databases have status ONLINE | |
| Long description: | A database state specifies the current running mode of that database. The database can be running in one state at a given time and there are seven main states in which a database can exist. The current state of a database can be checked by selecting the state_desc column of the sys.databases catalog view and can have following values: ONLINE, OFFLINE, RESTORING, RECOVERING, RECOVERY PENDING, SUSPECT and EMERGENCY. This alert can be configured to return values (warnings and alarms) based on different states of the database. | |
| Version: | 1.7 | |
| Default schedule: | * * * * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| ignore databases | model | List of databases (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters. |
| return status when OFFLINE | 2 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when a database is in state OFFLINE. |
| return status when RESTORING | 2 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when a database is in state RESTORING. |
| return status when RECOVERING | 2 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when a database is in state RECOVERING. |
| return status when RECOVERY PENDING | 2 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when a database is in state RECOVERY PENDING. |
| return status when SUSPECT | 2 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when a database is in state SUSPECT. |
| return status when EMERGENCY | 2 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when a database is in state EMERGENCY. |
| Ignore databases when AUTO CLOSE option is ON | NO | If set to YES all databases with AUTO CLOSE option turned ON will be ignored. |
| ignore standby databases | YES | Ignore databases with IsInStandBy = 1. |
| ignore OLD status | NO | Ignore databases if status (OFFLINE, EMERGENCY, RECOVERING, RESTORING or SUSPECT) have been registered for a period of time controlled by parameter “ignore status time threshold”. |
| ignore status time threshold | 96 | If the parameter “ignore OLD status” is set to “YES“ and the database has been in state OFFLINE, EMERGENCY, RECOVERING, RESTORING or SUSPECT for a period that exceeds the value of this parameter (in hours), then the database will be ignored. |
Job Summary
Purpose
- Monitors and reports on the status of databases, ensuring all are in the ONLINE state, and handles various database states such as OFFLINE, RESTORING, RECOVERING, RECOVERY PENDING, SUSPECT, and EMERGENCY.
Why
- This job is crucial as it:
- Helps in maintaining the availability and operational continuity of databases.
- Provides alerts on state changes that could indicate potential issues or the need for administrative intervention.
Manual Checking
- To manually check database states, the following SQL command can be issued:
SELECT name, state_desc FROM sys.databases;Implementation Details
- The monitoring job involves several SQL procedures and dynamic SQL scripts to check and log the state of databases.
- The job filters out databases based on specific criteria (like AUTO CLOSE option or STANDBY mode) and checks their current states against expected values.
Dependencies
- Relies on the internal history table ‘dbw_database_status_hist’ for maintaining state change records.
- Also depends on itself for successful monitoring execution and logging of database states.
Parameters Used
- Various parameters control which databases to ignore during monitoring and specify what alerts to raise based on certain database states (e.g., OFFLINE, RECOVERING). These parameters ensure targeted and relevant monitoring.
Tables and Reports
| Function | Description |
|---|---|
| Table Creation | Creates ‘dbw_database_status_hist’ for storing historical data of database states. |
| Historical Data Reporting | Reports last 50 state changes where state was other than ‘ONLINE’. Includes information like database, state, the duration of the state, and timestamps. |
Upgrades and Maintenance
- Ensures backward compatibility and introduces new parameters if missing.
- Periodically cleans up old entries from the history table to maintain performance and relevance.
Monitoring Job Outputs
- Generates detailed reports and alerts based on the current state of the databases.
- Provides a comprehensive log of activities and state changes helpful for audit and troubleshooting purposes.
Operational Handling
- In case of exceptions or errors during execution, appropriate alerts are raised, and error details are logged, ensuring visibility and swift resolution of issues.