In environments that rely on Microsoft SQL Server, the correct execution of automated tasks – known as jobs – is essential. In this article we explain what are jobs in MSSQL, why you should monitor them, and how to do it effectively using Zabbix. 

What are jobs in MSSQL? 🤔

In Microsoft SQL Server, jobs are automated tasks that run on a scheduled or on-demand basis. They are configured from the SQL Server Agent and allow one or more actions to be executed on a given schedule or in response to certain events, such as:

• Database maintenance: backups, index maintenance, integrity checks, cleaning of old logs.
• Data integration: ETL (Extract, Transform, Load) processes to load data from external sources, transform data and transfer it between different databases.
• Reporting: generate reports, aggregate and distribute data.
• Administrative tasks: running scripts, managing database objects and monitoring the health of the server.

Why is it important to monitor jobs? 👁️

Due to its advantages and utilities, it is normal to end up using this functionality a lot, in some cases too much, being used to ‘patch’ problems introduced by applications running on SQL Server. We also often see jobs that remain configured, even though they are no longer useful, but nobody dares to delete them. A good monitoring and inventory of jobs is important, as they are often tasks with a significant impact on the database.

Jobs are therefore a critical part of the database infrastructure. When they fail or are not executed in time, serious consequences can occur:

• Loss of recent backups.
• Outdated data in ETL processes.
• Unnecessary retention of logs or old data.
• Interruptions in dependent processes.

Monitoring jobs allows us to detect failures quickly, understand their frequency of execution and anticipate recurring problems.

How to monitor MSSQL jobs with Zabbix? 🔧

Zabbix’s default templates include job monitoring, so we can start there. If we have installed from version 5 onwards it will come pre-installed on the system, if not, we can easily download it from the official Zabbix website.

There will be two options:

1. Monitoring through Zabbix agent 2: for this option we must have agent 2 installed on our machine. This method will not be useful if we have a database as a service in Azure or AWS, for example, but if it is our own machine, in the cloud or on-premise, we will probably already have the agent installed to monitor the different metrics of the operating system itself, which will logically help to correlate with the different events of the database monitoring.
   
2. Monitoring through ODBC: the most versatile option, although it implies making the database port accessible to the Zabbix server or proxy we are using, as well as having FreeTDS and ODBC installed in the server or proxy.
   For example, on RedHat / CentOs / AlmaLinux / RockyLinux:
   

dnf install epel-release
dnf install unixODBC unixODBC-devel freetds

💡 Here is a link with more details for this part: https://www.zabbix.com/documentation/7.0/en/manual/config/items/itemtypes/odbc_checks

In both cases, we will need to create a user (e.g. zbx_monitor) that has the following permissions:

• View Server State
• View Any Definition

Besides, in order to be able to monitor the jobs, we need to run:

GRANT SELECT ON OBJECT::msdb.dbo.sysjobs TO zbx_monitor;
GRANT SELECT ON OBJECT::msdb.dbo.sysjobservers TO zbx_monitor;
GRANT SELECT ON OBJECT::msdb.dbo.sysjobactivity TO zbx_monitor;
GRANT EXECUTE ON OBJECT::msdb.dbo.agent_datetime TO zbx_monitor;

Once we configure the keys and the server data in the templates, we will be able to see the first data. Jobs are something that is ‘discovered’, and here we can see the data that will be obtained for each job:

• Whether it is enabled or not
• The last time it was launched
• The last status message
• The next time it will be launched
• The duration of the last time it was launched
• The status of the last time it was launched, which can be:
  • Failed
  • Succeeded
  • Retry
  • Canceled
  • Running

With this monitoring we already have the possibility to see quite a lot of things:

• Alerts for failed jobs
• Alerts for jobs that have taken more time than they should have
• We can analyze the times and detect those processes that take more time, and how they do it, by looking at their statistics (maximum, minimum, average, percentiles, etc.). If we also plot this on a heat map or histogram, we can visually detect anomalies.

Apart from the default Zabbix template, there is something that can be very interesting, which is to have a view of when our jobs are executed and how long they last, thus being able to visualise their sequencing:

To do this, we have to use a query of this style:

SELECT
    ja.job_id AS JobID,
    j.name AS JobName,
    '1' as IsRunning
    --ja.start_execution_date,      
    --ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
    --Js.step_name
FROM msdb.dbo.sysjobactivity ja 
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
    ON ja.job_id = js.job_id
    AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE
  ja.session_id = (
    SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC
  )
AND start_execution_date is not null
AND stop_execution_date is null;

By monitoring the result of this query we can obtain the sequencing of our jobs. This monitoring is of course included in the templates we use in Minerva, along with a complete Grafana dashboard that includes the rest of the Microsoft SQL Server metrics: full scans, users, pages, locks, etc. 

Conclusion ✍️

Job monitoring in MSSQL is not only a best practice, but a necessity in environments where databases play a critical role in business processes.

Thanks to tools such as Zabbix and Minerva, it is possible to obtain complete visibility of their status, performance and possible failures, allowing you to react proactively to any anomaly.

Correct configuration and visualisation not only helps to detect errors, but also to optimise execution times and ensure the reliability of scheduled tasks. This translates into greater operational stability, less manual workload and a more predictable and maintainable infrastructure… but as you can see, it takes a certain amount of work and time that is often not available on a day-to-day basis.

Luckily, at Muutech we offer platform and service, so that you can just focus on tackling the jobs as soon as they fail, checking sequences and those slow ones that collapse your database and annoy your users.

And you, what else do you think it would be interesting to monitor? As you can see, the database has a good record of the activity of the jobs, their steps, results, current status, etc, although it is complex to manage and not very well documented.

Do you have any questions? Would you like a hand? 👇