SQL Server Agent status
Job details
| Name: | SQL Server Agent status |
| Platform: | Sqlserver |
| Category: | Availability |
| Description: | Checks if the SQL Server Agent is running. |
| Long description: | This alert checks the master.dbo.sysprocesses table for the program_name value for the SQL Server Agent process. The program name value is controlled by the dbWatch parameter ‘sqlserver agent process name’. Besides this parameter values, the following program names are checked too: ‘SQLAgent – Generic Refresher’ and ‘SQLAgent – Job invocation engine’. |
| Version: | 1.3 |
| Default schedule: | 0,15,30,45 * * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘1999′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’ & eng_inst_priv = 0] |
Parameters
| Name | Default value | Description |
|---|---|---|
| sqlserver agent process name | SQLAgent – Generic Refresher | The program_name column value for the SQL Server Agent as it appears in the master.dbo.sysprocesses table. |
| return status | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when SQL Server Agent is not running. |
Job Summary
- Purpose: The purpose of this job is to monitor the status of the SQL Server Agent to ensure it is running.
- Why: This job is important because the SQL Server Agent is responsible for running scheduled jobs and various automated tasks within SQL Server. If the SQL Server Agent is not running, scheduled jobs will not execute, which can affect system operations and maintenance tasks.
- Manual checking: You can check the status of the SQL Server Agent manually in the database by issuing the following SQL command:
SELECT name, status, last_run_outcome, last_run_date, last_run_time FROM msdb.dbo.sysjobs;Report Presentation Structure
The job report provides a detailed view from the sysprocesses table about the SQL Server Agent status. This includes the following information in a structured table format:
| SPID | Login Time | Last Batch | Status | Program Name | Command | NT Username | Loginame |
|---|
Here, variables like SPID, Login Time, Last Batch, and others give a comprehensive insight into the processes related to the SQL Server Agent, helping in monitoring and troubleshooting.
Implementation Details
- The job consists of a stored procedure that checks if the SQL Server Agent processes like “SQLAgent – Generic Refresher” or “SQLAgent – Job invocation engine” are present in the master.dbo.sysprocesses table based on the program name parameter.
- If the SQL Server edition is not Express, Developer, or Desktop, and if there are enabled jobs, the procedure asserts if the agent is running or not. If no matching processes are found, it returns a non-zero status indicating a problem.
Auto-Installation and Upgrades
- This monitoring job includes scripts for automatic installation and upgrading to newer versions, ensuring compatibility and up-to-date functionality with newer database environments, as specified in the job configuration.
- SQL script updates involve adjustments in handling returned values and messages based on the SQL Server’s edition and job status, catering to dynamic conditions and specific requirements for different server setups.
This structure and functionality are designed to maintain the high availability and reliability of SQL Server instances by ensuring the SQL Server Agent is active and operational, aligning with system automation and scheduled tasks management.