To check SQL Server Agent jobs, you can use the built-in graphical tools in SQL Server Management Studio (SSMS) or run T-SQL queries for more detailed or automated reporting.

1. Using SQL Server Management Studio (SSMS)

The easiest way to see what is happening right now is the Job Activity Monitor.

1. Open SSMS and connect to your instance.

2. Expand the SQL Server Agent folder in the Object Explorer.

3. Right-click Job Activity Monitor and select View Job Activity.

• What you’ll see: A sortable list of all jobs, their current status (Running, Idle, etc.), the last run outcome, and the next scheduled run time.

• Action: You can right-click any job in this list to Start, Stop, or View History.

2. Using T-SQL Queries

If you need to check job status via script or for a quick health check, use these queries against the msdb database.

View Currently Running Jobs

This script shows you only the jobs that are actively executing right now:

USE msdb;
GO
SELECT 
    j.name AS [Job Name],
    ja.start_execution_date AS [Start Time],
    ISNULL(last_executed_step_id, 0) + 1 AS [Current Step]
FROM sysjobactivity ja
JOIN sysjobs j ON ja.job_id = j.job_id
WHERE ja.session_id = (SELECT MAX(session_id) FROM syssessions)
  AND ja.start_execution_date IS NOT NULL
  AND ja.stop_execution_date IS NULL;

Check Recent Job History (Last Run Status)

To see which jobs failed or succeeded recently:

SELECT 
    j.name AS [Job Name],
    run_date,
    run_time,
    CASE run_status 
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
    END AS [Last Run Status],
    message AS [Status Message]
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE step_id = 0 -- 0 indicates the overall job outcome
ORDER BY run_date DESC, run_time DESC;

3. Troubleshooting “Agent Not Running”

If you see a small red x on the SQL Server Agent icon in SSMS, the service is stopped, and no jobs will run.

To Start: Right-click SQL Server Agent and select Start.

Via T-SQL: You can check if the service is alive with:

EXEC xp_servicecontrol N'querystatus', N'SQLServerAgent';