Program status
Job details
| Name: |
Program status |
| Platform: |
Sqlserver |
| Category: |
Availability |
| Description: |
Checks for any program connected to the SQL Server by checking the program_name column in the master.dbo.sysprocesses table. |
| Long description: |
|
| Version: |
2 |
| Default schedule: |
0 * * * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘1999′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ |
engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name |
Default value |
Description |
| program name |
test |
The program_name column value the way it appears in the master.dbo.sysprocesses table. |
Job Summary
- Purpose:
- The purpose of this monitoring job is to check the status of a specified program by monitoring its presence in the master.dbo.sysprocesses table of a SQL Server or Azure SQL Managed Instance environment.
- Why:
SELECT program_name, count(*) as 'Number of Instances' FROM master.dbo.sysprocesses WHERE program_name = '<your_program_name>' GROUP BY program_name;
Job Configuration Details
- Dependency:
- This task depends on itself to ensure continuous monitoring, with a clean-up action set to activate upon task failure.
- Implementation code:
Reporting
- Report Template:
- The report generated will list details such as session ID, login time, last batch execution time, status, program name, command, NT username, and login name for each instance where the specified program is detected.
| SPID |
Login Time |
Last Batch |
Status |
Program Name |
Command |
NT Username |
Loginame |
| 101 |
2023-01-02 12:10:00 |
2023-01-02 12:15:00 |
running |
YourProgram |
SELECT |
user1 |
user1login |
| 102 |
2023-01-02 12:20:00 |
2023-01-02 12:25:00 |
running |
YourProgram |
UPDATE |
user2 |
user2login |
- Output Text:
- The report header states “Program status”, and the description includes the detailed results from the sysprocesses table, such as the number of logins associated with the specified program.
Additional Monitoring and Maintenance Features
- Acknowledgements Feature:
- An acknowledgements feature is enabled for this job to ensure that all alerts and anomalies detected are acknowledged by the responsible IT personnel or system administrators.
- Express Installation:
- This job does not support express installations, indicating a standard setup process is required for implementation.
This robust monitoring setup provided by dbWatch Control Center ensures enhanced control and proactive management of database programs essential for maintaining optimal operational functionality.