Report Server status
Job details
| Name: | Report Server status | |
| Platform: | Sqlserver | |
| Category: | Availability | |
| Description: | Checks if the program ‘Report Server’ is connected to the SQL Server by checking the program_name column in the master.dbo.sysprocesses table. | |
| Long description: | Checks if the program ‘Report Server’ is connected to the SQL Server by checking the program_name column in the master.dbo.sysprocesses table. | |
| Version: | 2 | |
| Default schedule: | 0 * * * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2008′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| program name | Report Server | The program_name column value the way it appears in the master.dbo.sysprocesses table. |
| warning threshold | 10 | Maximum number of minutes before a warning is returned if the Report Server is not connected to the SQL Server instance. |
| alarm threshold | 30 | Maximum number of minutes before an alarm is returned if the Report Server is not connected to the SQL Server instance. |
Job Summary
- Purpose: The primary purpose of this monitoring job is to ensure that the “Report Server” application is connected to the SQL Server instance and to log its connectivity status.
- Why: This job is crucial as it helps in proactively monitoring the connection status of the Report Server. It checks and logs the time of the last connection, and provides alert levels (warning and alarm) based on the duration of disconnection, which could indicate possible issues in the application or database connectivity that need attention.
- Manual checking: To manually check the connection status of the Report Server to the SQL database, you can examine the contents of the table created and updated by this job (dbw_report_server_stat_tab) and query the master.dbo.sysprocesses table for the latest connection details:
SELECT program_name, login_time FROM master.dbo.sysprocesses WHERE program_name = 'Report Server' ORDER BY login_time DESC;Implementation Details
The monitoring job operates in several key operational steps:
- Creation of a status table (dbw_report_server_stat_tab) to log when the Report Server was last connected.
- Regular checks on the connection status by querying the sysprocesses table for the presence of ‘Report Server’. If not found, it notes the last connected time and updates it if the program is running.
- Setting different return statuses based on warning and alarm thresholds which are determined by the length of time since the last successful connection recorded in the status table.
Alert Configuration
- Warning: Triggered if the disconnection duration exceeds the “warning threshold” but stays below the “alarm threshold”.
- Alarm: Triggered when disconnection duration surpasses the “alarm threshold”.
Report and Alert Details
The monitoring system not only checks and updates the internal data tables but also manages a customer-facing report that provides:
- A table view showing the current active sessions from the program ‘Report Server’ within SQL Server, including details about SPID, login time, status, etc.
- A historical view that lists details about warnings and alarms, including timestamps and acknowledgment statuses.
| Item | Description |
|---|---|
| Program status check | Ensures whether ‘Report Server’ is listed in the sysprocesses table or not. |
| Last connection log | Tracks the last time ‘Report Server’ was connected. |
| Warnings and alarms | Generates alerts based on predefined thresholds of allowed disconnection time. |
This job configuration plays a vital role in maintaining the reliability and availability of applications depending on stable database connectivity, helping database administrators to manage and anticipate potential connection issues with actionable data.