Database status
Job details
| Name: |
Database status |
| Platform: |
Sqlserver |
| Category: |
Availability |
| Description: |
Checks if all databases have status ONLINE |
| Long description: |
|
| Version: |
2 |
| Default schedule: |
0 * * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version = ‘2000′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’] |
Parameters
| Name |
Default value |
Description |
| ignore databases |
model |
List over the databases (separated by comma) which will be ignored from being checked. |
Job Summary
- Purpose: The purpose of this job is to monitor the status of databases in a Microsoft SQL Server 2000 instance and flag any databases that are not online.
- Why: This job is important because it ensures that all databases are operating normally and are accessible. If databases are not online, it may indicate serious issues such as data unavailability or system failures, which could affect operational continuity.
- Manual checking: To manually check the database statuses, you can issue the following SQL command to retrieve the status of all databases:
SELECT name, CONVERT (VARCHAR(20), DATABASEPROPERTYEX(name, 'Status')) AS status FROM master.dbo.sysdatabases ORDER BY name
Job Details
Target Database
- The job targets instances of Microsoft SQL Server (version 2000) which have the database engine installed.
Dependencies
- This job is dependent on the “dbw_database_status” task.
- If the job fails, it is configured to perform cleanup operations to revert any partial changes made during its execution.
Implementation
- The job is implemented via a SQL procedure named “dbw_database_status”.
- The procedure checks the status of each database against a list of databases to be ignored (by default, the “model” database).
- Databases not in the ignore list are monitored to ensure their status is “ONLINE“.
- If any database is not “ONLINE“, the job returns an error and captures details regarding which databases are affected.
Reporting
- A report is generated listing all databases along with their statuses.
- The report includes details such as database ID, name, status, and creation date.
Presentation
| Database ID |
Name |
Status |
Created Date |
| 1 |
SampleDB |
ONLINE |
2000-04-01 |
| 2 |
TestDB |
OFFLINE |
2000-06-15 |
- The table above provides a simplified view of the report format which lists database details including each database’s online status.
Configuration
- The job is configured to run every hour.
- Any critical status change detected triggers an update within dbWatch Control Center, notifying system administrators or relevant stakeholders of the current database status and facilitating prompt action to resolve any issues.
Additional Information
- The job and its reports are essential tools for database administrators to maintain system health and data availability on SQL Server 2000 platforms.