Database status
Job details
| Name: |
Database status |
| Platform: |
Sqlserver |
| Category: |
Availability |
| Description: |
This alert checks if the instance has been restarted since the last check |
| Long description: |
|
| Version: |
2 |
| Default schedule: |
5m |
| Requires engine install: |
No |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine =‘NO’ & (engine_edition = ‘Microsoft SQL Server’ |
engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name |
Default value |
Description |
| ignore_databases |
2 |
List of databases (separated by comma) which will be ignored. |
| return_status_value |
2 |
Return status value (ALARM – 2, WARNING – 1, or OK – 0) |
Job Summary for MS SQL Server Database Status Alert
- Purpose:
- The purpose of this monitoring job is to check the status of MS SQL Server databases to ensure they are all in an ‘ONLINE’ status.
- This job assesses each database’s operational status and reports any deviations, specifically databases that are not currently ONLINE.
- Why:
- This job is critical for ensuring the availability and accessibility of databases on the SQL server. An ‘ONLINE’ status indicates that the database is active and operational, meeting the basic criteria for accessibility and serviceability.
- Monitoring and verifying the ONLINE status helps to identify potential issues early, reducing downtime and service disruptions.
- When databases are not ONLINE, this can be indicative of potential issues that could affect the reliability and performance of business operations. Alerts for these events allow for quick action and troubleshooting.
- Manual checking:
- To manually check the status of databases in MS SQL Server, you can run the following SQL query in the management studio:
SELECT name, dbid, CONVERT(VARCHAR(20), DATABASEPROPERTYEX(name, 'Status')) AS status FROM master.dbo.sysdatabases
Detailed Operation
- This monitoring job is designed to work with instances where the ‘type’ equals ‘instance’, the ‘databasetype’ is ‘sqlserver’, and specific version or feature conditions are met (SQL Server major version greater than 2000, engine edition set to Microsoft SQL Server or Azure SQL Managed Instance).
- The job:
** Executes every 5 minutes by default.
** Collects and evaluates the database statuses by running a query to fetch the database name and current status from the system catalog view in MS SQL.
** Databases specified in the ‘ignore_databases’ parameter are excluded from negative reporting even if their statuses are not ONLINE.
- Report Generation:
- Details and summary of database statuses are compiled into a report by this monitoring job. Databases that deviate from the expected ‘ONLINE’ status are highlighted in a table format, aiding quick visibility and response.
Output Tables and Details
| Database Name |
Current Status |
Action |
| Database A |
OFFLINE (Ignored) |
No action needed, ignored |
| Database B |
ERROR |
Investigate and resolve |
- The table indicates the action suggested based on the database status returned. For instance, an ‘OFFLINE’ status might not require action if the database had been pre-listed to be ignored in the monitoring settings.
- Summary Information:
- The final output also provides an overall count of databases checked and details any that were ignored based on the settings. This summary helps in understanding the environment’s overall health and potential scope of the issue.
This configuration and the monitoring job design ensure proactive management of database health, thus supporting continuity and performance of business operations tied to these databases.