Seventh SQL Server Agent job Activity Monitor

This article is the seventh article of the SQL Server Agent series, please refer to the original text for details

In the first few of these series, you create and configure SQL Server Agent jobs. Each job has one or more steps and may contain a large number of workflows. In this article, you will see the job Activity Monitor. The job Activity monitor is a system administration tool to run work, view job history, and enable/disable jobs. This article also reviews some of the stored procedures that are called by the job Activity monitor, and you can directly invoke the process to do your own custom task monitoring.
SQL Server Agent Job Activity Monitor
The primary mechanism for the SQL Server Agent job Activity Monitor is to monitor job runs and to view jobs that the agent has run. The job Activity Monitor is a separate dialog box that the job Activity Monitor uses to display SQL Server Agent job status using system stored procedures and system tables.
Using the Job Monitor
Start SSMs, navigate to the SQL Server Agent node under Object Explorer. You should see the job Activity Monitor, shown in 7.1. Double-click the job Activity Monitor, or right-click the menu item and select View job activity, and you will see the job Activity Monitor start. You may notice that the title also contains the server name on the SSMS connection. In my example, the use is "(local)" (7.2).
Figure 7.1 Starting Job Activity Monitor

Figure 7.2 Job Activity Monitor dialog box
Under the job Activity monitor, you can see all the agent jobs defined on the instance. You can see which jobs are enabled, whether they are running recently, whether the job is running, whether the last run result was successful, the next run time for the recurring job, and if your job has a category, the category will also be displayed.
Each column in the agent job activity is sortable-click the name of the column. For example, you might want to sort by job category. The job category is one we did not talk about the topic, so we will briefly explore the categories of jobs created and specified.
Job category
It is useful to group jobs with job categories in the job Activity monitor. There are some built-in system job/task job categories. You can also create your own job categories. Ssms->sql Server Agent---Jobs, right-click the job, and select Manage Job categories. This launches the Manage Job Category dialog box, shown in 7.3.

Figure 7.3 Managing Job Categories
Click Add ... button to create a new job category. Name Your job category ("Backups" in this example), and then click the Show all Jobs check box to list your current SQL Server Agent jobs. Tick the backup master job, as shown in 7.4. You can also assign job categories later by editing the job properties.

Figure 7.4 Creating a job category and assigning jobs
Click the Add button again, add a second category, name "Examples", and select other sample jobs created by this series. Click OK, and then click Cancel to close the dialog box (don't worry, it won't undo the previous action, it just closes the Manage Job Category dialog box).
Job Activity Monitor Filter settings
Now you have some interesting data filtering and sorting, re-opening or returning to the job Activity monitor. If you click on the title of "category" in the grid, you will see the job sorted by the newly created category (7.5).
Figure 7.5 Sorting by Category
You can sort by any display column, but when you add more jobs, the information in the dialog box becomes more and more difficult to find the job you need.
At the top of the dialog box, you can choose to refresh or filter the dialog box. If you have 100 or more jobs (I know many DBAs manage many jobs), you might use the Filter Settings dialog box to limit the jobs you want to see. Click Filter and you will see the Filter Settings dialog box. Enter backups (7.6) in the category and tick the Apply Filter check box at the bottom of the dialog box. Click OK, and you will see figure 7.7 showing only jobs with the job category backups.

Figure 7.6 Job Activity Monitor filter settings
Figure 7.7 Job Activity Monitor app filter
Note that on the left side of the dialog box, you will see a filter being applied. If you want to see all the work again, you must click Filter again to cancel the "Apply Filter" option.
use job Activity Monitor to monitor jobs
You can set the job Activity Monitor to refresh automatically so that you can see the failed jobs in time. In the Job Activity monitor, click "View Refresh Settings" And you will see the Refresh Settings dialog box shown in 7.8. In this example, tick the auto-refresh interval and change the seconds from the default of 60 to 30 (depending on your monitoring).
Figure 7.8 Job Activity monitor setting automatic refresh
Click OK, and now the dialog box will automatically refresh every 30 seconds. In combination with filtering or sorting in the last run result, it is very easy to navigate to the failed job.
Finally, if you want to manage jobs (that is, enable, disable, run jobs, or modify job properties), simply right-click the job in the job Activity monitor. These are similar to the previous SQL Server Agent step articles, so they will not be repeated again.
system tables and stored procedures under Job Activity Monitor
The job Activity monitor corresponds to some system tables and system stored procedures. When SQL Server Agent starts, a new row is inserted into the Msdb.dbo.syssessions table, recording the time that SQL Server Agent started, session_id. Every time a service is started, all existing SQL Server Agent job snapshots are inserted into the msdb.dbo.sysjobactivity table. The table is used to record information in the job Activity monitor, such as the last run time of the job and the next run time. In addition, a pointer to the job history (job_history_id column) corresponds to the Msdb.dbo.sysjobhistory table.
In addition, the job Activity Monitor also includes tables from other systems, such as Msdb.dbo.sysjobs, Msdb.dbo.sysjobsteps, and some extended stored procedures such as master.dbo.xp_sqlagent_enum_jobs. All these tables and procedures are wrapped in the system stored procedure msdb.dbo.sp_help_job.
If you want to monitor your homework but don't use ssms, you can simply run sp_help_job. You can create a job that will sp_help_job run results messages to your phone.
Next Article
The SQL Server Job Activity Monitor allows you to flexibly use the graphical interface to monitor job activity and job progression. You can sort, categorize, and filter the jobs you're interested in. You can also run the system store if you do not want to use the graphical interface.
In our next article, we'll explore how to use external programs in SQL Server Agent, such as operating system or PowerShell commands.

Seventh SQL Server Agent job Activity Monitor