How to automate monitoring and alerting on SQL Server Agent status changes

SQL Server Agent overview

The main purpose of SQL Server Agent is executing administrative tasks within SQL Server, mostly as on-demand user actions. It is Windows service which runs continuously in the background, but it stores necessary information within SQL Server itself, in the msdb system database.

SQL Server Agent consists of three main components, which separately provide the core functionality including: jobs, alerts and operators.

  • Jobs are pre-configured sequence of steps. They can be scheduled to run in specific time span or particular times as a single action. Also, they can be monitored, in order to track their success or failure. When job is running, there is one subcomponent related sub-component, schedules.
  • Alerts provides information on particular events which occur in SQL Server, and take corresponding actions, like sending email automatically, or executing a job.
  • Operators, as user roles, which maintain the job/alert ecosystem of SQL Server. Usually, operators are notified on alerts and status of the service and jobs.

The main focus of this article is SQL Server Agent service status, whether it is running, or it stopped, which can be determined by using various techniques.

Checking SQL Server Agent status manually

There are several ways to determine SQL Server Agent status, manually or programmatically:

  • In Windows Services: Run Services.msc, and seek for a desired SQL Server instance:

If the service is stopped (like the first one on the picture above), that information is visible by double-click on the particular service, in Properties dialog:

  • In SQL Server Configuration Manager:

  • In SQL Server Management Studio – In Object Explorer dock, within desired SQL server instance:

If service is stopped, the icon will appear like this (in SSMS 2016 version):

  • With T-SQL – Use the displayed script on desired instance:
IF EXISTS (SELECT 1 FROM sysprocesses WHERE LEFT(program_name, 8) = 'SQLAgent')
PRINT 'SQL Server Agent is running.'
ELSE
PRINT 'SQL Server Agent stopped.';

The result of query will be one of the messages stated in query, whether SQL Server Agent service is running or not.

  • With PowerShell:
Get-Service | where-object {$_.name -like '*SQL*AGENT*'}

It will display results like this:

This PowerShell script reads all services that have strings „SQL“ and „AGENT“ in their titles and list them with their statuses, but, if the exact name of the service is known, use this script:

Get-Service -name <name_of_the_service>

These methods will ascertain the status of the service but won’t proactively alert the user nor will it provide a log of any changes

To log the status, one way is to create a job within SQL Server Agent, which will include a specified PowerShell script and generate a Text file with information on the service's status every time when service starts or restarts.

Creating an automated SQL Server agent Status change log job

Expand the SQL Server Agent icon, and Jobs folder, as well. Right-click and pick the New Job option:

In the General tab, input name of the job (in this case SQL Agent status change log), set the owner (user with Administrator role), set Category to „Data Collector“, and optionally, provide a job description, as in picture below.

In Steps tab, create step by clicking New button.

Job Step Properties dialog will appear. In General tab, input the step name (Status Change Notification), choose PowerShell for Type option, because this step will execute particular script (below), set Run as option as displayed (job will run under default user).

Invoke-Sqlcmd "IF EXISTS(
SELECT 1
FROM MASTER.dbo.sysprocesses
WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
SELECT SERVERPROPERTY('ServerName') AS 'InstanceName', 'Running' AS 'SQLServerAgent Status'
END
ELSE
BEGIN
SELECT SERVERPROPERTY('ServerName') AS 'InstanceName', 'Stopped' AS 'SQLServerAgent Status'
END" | Out-File "d:\sql_agent_status.txt"

Included PowerShell script will retrieve instance name and status of related SQL Server Agent service, with exporting the results in Text file (which path can be modified according to needs).

In the Advanced tab (Job Step Properties dialog), set these options like in picture below:

The created step will appear in the dialog further, and declared as a starting one:

In Job Properties dialog, Schedules tab, create the new schedule:

In the following dialog, set the Schedule type option like in picture below:

This particularly means that the job will execute every time SQL Server Agent service starts (on boot, if restarts or stopped and started again, e.g.). Also, the schedule will display in the dialog:

After creation, the new job will appear in the Jobs list:

To test the SQL Agent status change log job, simply execute it with right-click, Start Job at Step option. After successful attempt, the confirmation dialog will appear:

In this case, generated Text file should appear on the specified path, containing the information on service’s status. Otherwise, check all the steps again, in order to identify errors in job creation.

Output looks like this:

As can be noticed, so far, there was no mentioning of actual SQL Server Agent status active monitoring, only manual retrieving the information on the services's status and executing job on specified event.

Continuous monitoring for changes

ApexSQL Monitor is a SQL Server performance monitoring tool but it also has a feature for the active monitoring of SQL Server Agent service status.

ApexSQL Monitor indicates the current status of SQL Server Agent service, tracks the exact moments when this service changed status and displays that information in the multiple views.

Basic monitoring of the SQL Server Agent status is solved by using special performance counter, which is placed in Metrics view, under SQL Server metrics group:

Notice that this counter has no settings for baseline threshold, particular threshold levels, and custom alert period, because ApexSQL Monitor just checks its status in defined period (by default, it is 15 seconds).

A passive approach in determining the status changes is to first review and then resolve alerts related to SQL Server Agent event(s).

Go to the Alerts view, and seek for the SQL Server Agent status thread:

The difference between an “ordinary” metric and this one, is that scale of values for SQL Server Agent status graph are descriptions of status changes, instead of numeric values: Stopped, Other start pending, Other stop pending, Running and Other continue pending. In this particular graph, the events of stopped service and running in continuous manner are present.

You can review all alerts related to this metric in Details tab. The exact time of occurring event can be seen in the dock (in Time raised column) or by hovering on specific spot on the graph itself (marked in the picture below).

These alerts can be periodically checked, in order to determine the frequency of status changes.

Proactive alerting on SQL Server Agent status changes

Along with these basic steps, user can set and apply some specific alert actions to this metric, by utilizing email notifications, custom SQL script and custom command alert actions. Once set, these actions can notify the user (and emulate sending information to operator), reveal the possible reason of the service stopping, and start/restart the service programmatically.

Setting the email notification

To get email notifications on status change, set the desired email profile and apply it to the metric:

Email notifications can be set to send a summary email in a desired time span, or when some alert is triggered. To accomplish this, choose the second option when setting the email profile, and it will send an email every time status of the service changes:

Start/Restart SQL Server Agent Service with PowerShell

Use this PowerShell script and embed it in custom command alert action, and it will check the status of the service on every change, whether is stopped or in pending change (as stated above):

powershell.exe "if ((Get-Service -name SQLSERVERAGENT).Status -eq 'Stopped'){Start-Service SQLSERVERAGENT}
if ((Get-Service -name SQLSERVERAGENT).Status -eq 'Running'){Restart-Service SQLSERVERAGENT}"

Instead of SQLSERVERAGENT, other name of the service can be input.

 

December 6, 2016