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.
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
