SQL Agent is a component of Microsoft SQL Server that is responsible for automating administrative tasks and scheduling jobs. It provides a framework for creating jobs that can perform a wide range of tasks such as executing SQL scripts, sending notifications, and running SSIS packages.
The SQL Agent is a service that runs continuously in the background and can be configured to start automatically when the server starts up. It has a central management console in SQL Server Management Studio (SSMS) that allows administrators to create, schedule, and manage jobs. The SQL Agent also includes features for monitoring the status of jobs, setting up alerts for specific events, and logging job history.
Using the SQL Agent, administrators can schedule recurring tasks such as backups, index maintenance, and data synchronization. Jobs can be scheduled to run at specific intervals, on specific days of the week or month, or in response to specific events. The SQL Agent also supports the creation of multi-step jobs that execute a sequence of tasks in a specific order.
Overall, the SQL Agent is a powerful tool for automating administrative tasks in SQL Server, reducing manual effort and ensuring that critical tasks are performed reliably and on schedule.
More Information On The SQL Agent Can Be found Here
What Is The MSDB Database And SQL Server Agent service
The MSDB system database is a system database in Microsoft SQL Server that is used to store and manage a variety of system objects, including system tables, stored procedures, and schedule information for SQL Server Agent.
SQL Server Agent is a component of SQL Server that is responsible for scheduling and automating tasks such as backups, maintenance jobs, and other administrative tasks. It provides a way to automate and manage these tasks through a graphical user interface (GUI) or through Transact-SQL (T-SQL) scripts.
The MSDB database plays a critical role in SQL Server Agent by storing job definitions, job history, and other metadata related to SQL Server Agent. This allows administrators to view and manage job execution, review job history, and set up notifications when jobs succeed or fail.
In addition to job scheduling and automation, SQL Server Agent provides a wide range of functionality for managing SQL Server, including managing replication, performing database maintenance, and configuring alerts and notifications. The MSDB database is an integral part of SQL Server Agent, providing the storage and management capabilities needed to support these features.
The MSDB database is a system database in Microsoft SQL Server that is used to store various system objects and metadata related to SQL Server Agent jobs, database mail, service broker, maintenance plans, and other system-related features. Some of the key components of the MSDB database are:
SQL Server Agent:
The MSDB database stores information about SQL Server Agent jobs, job schedules, alerts, and operators. This information is used to schedule and monitor tasks such as backups, database maintenance, and other automation tasks.
Database Mail:
The MSDB database stores configuration settings and messages for SQL Server Database Mail, which is used to send email messages from SQL Server.
Maintenance Plans:
The MSDB database stores configuration settings for SQL Server Maintenance Plans, which are used to automate database maintenance tasks such as backups, index maintenance, and database integrity checks.
Service Broker:
The MSDB database stores metadata for Service Broker, which is a messaging and queuing technology that allows applications to send and receive messages within SQL Server.
Overall, the MSDB database plays an important role in the management and automation of various system-related tasks in SQL Server.
Using A Custom Report "SQL Agent Job"
Querying the MSDB and getting information about the jobs and schedules is a bit challenging as it requires joining multiple system tables together in order to generate a meaningful report. I have found that this custom report can be handy for viewing a job's daily Frequency and schedule type, here is how to run the report.
SQL Server Management Studio (SSMS) allows you to create custom reports to display data that is not available in the default reports. Here are the steps to create and use a custom report in SSMS:
Open SSMS and connect to a SQL Server instance.
In the Object Explorer, right-click the server node, and select "Custom Reports" from the context menu.
In the "Custom Reports" dialog box, click "New Report".
In the "Report Designer" window, you can create your report by selecting the data source, designing the layout, and adding fields and expressions as needed.
Save the report file (.rdl) to a location on your computer or on a report server.
In the "Custom Reports" dialog box, click "Add".
In the "Add Custom Report" dialog box, specify a name for the report and the path to the report file.
Click "OK" to save the report definition.
To view the report, right-click the server node in Object Explorer, select "Custom Reports", and select the report from the list.
Once the custom report is added, you can run it to view the data. You can also customize the report by adding parameters, sorting and filtering options, and more. Custom reports can be used to display various types of data, such as server configuration information, database usage statistics, and performance metrics.
Download the report here; this will list jobs and their status
Query Currently Running Jobs
Double-click on Job Activity Monitor to Open the Job window You can see the current Job Step that is running
To query currently running jobs in SQL Server, you can use the sys.dm_exec_sessions and sys.dm_exec_requests dynamic management views to find sessions that are associated with a SQL Server Agent job. Here's an example query:
SELECT
s.session_id,
s.login_time,
j.name AS job_name,
r.command,
r.status
FROM sys.dm_exec_sessions AS s
INNER JOIN msdb.dbo.sysjobs AS j
ON CAST(s.program_name AS NVARCHAR(128)) LIKE '%' + j.name + '%'
INNER JOIN sys.dm_exec_requests AS r
ON s.session_id = r.session_id
WHERE r.command LIKE 'EXEC%'This query returns a list of all currently running jobs in SQL Server, along with information about the session, the job name, the command being executed, and the status of the command. Note that this query uses a LIKE operator to match the job name with the program name associated with the session, so it's possible that it may return false positives if there are multiple jobs with similar names.
It's also worth noting that this query only works if the job is actively running. If the job is scheduled to run at a later time, it will not appear in the results until it actually starts running.
To Query Job History
To query SQL Server Agent job history, you can use the msdb.dbo.sysjobhistory system table. Here's an example query:
SELECT
j.name AS job_name,
h.run_date,
h.run_time,
CAST(CAST(h.run_date AS VARCHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS DATETIME) AS start_time,
CAST(CAST(h.run_date AS VARCHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(h.run_time + h.run_duration AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS DATETIME) AS end_time,
h.run_duration/10000 AS duration_in_seconds,
CASE h.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS run_status,
h.message
FROM msdb.dbo.sysjobhistory AS h
INNER JOIN msdb.dbo.sysjobs AS j
ON h.job_id = j.job_id
ORDER BY h.instance_id DESC
This query retrieves job history information from the msdb.dbo.sysjobhistory table, including the job name, start time, end time, duration in seconds, run status, and message. The run_date and run_time columns are combined to create a DATETIME value for the start and end times using the STUFF function to insert colons in the appropriate positions. The run_duration column is divided by 10000 to convert it from the stored value in tenths of a second to seconds.
The results are ordered by instance_id in descending order to show the most recent job executions first. You can modify the query to filter the results based on specific jobs, dates, or other criteria as needed.
SQL Job Schedule Query
To query the list of SQL Agent jobs using T-SQL, you can use the following query:
This query retrieves the job ID, name, description, and enabled status of all SQL Agent jobs that exist on the server. The msdb.dbo.sysjobs system table contains information about SQL Agent jobs, including their properties and history.
Adding Syslogins To The Query
You can also add additional columns to the SELECT statement to retrieve more information about each job, such as the date the job was last executed, the next scheduled execution time, or the owner of the job. For example:
SELECT job.job_id, job.name, job.description, job.enabled,
job.last_run_date, job.last_run_time, job.next_run_date, job.next_run_time,
owner.name AS owner_name
FROM msdb.dbo.sysjobs job
JOIN msdb.dbo.syslogins owner ON job.owner_sid = owner.sidThis query retrieves the job ID, name, description, and enabled status of all SQL Agent jobs that exist on the server. The msdb.dbo.sysjobs system table contains information about SQL Agent jobs, including their properties and history.
Adding Syslogins To The Query
You can also add additional columns to the SELECT statement to retrieve more information about each job, such as the date the job was last executed, the next scheduled execution time, or the owner of the job. For example:
SELECT job.job_id, job.name, job.description, job.enabled,
job.last_run_date, job.last_run_time, job.next_run_date, job.next_run_time,
owner.name AS owner_name
FROM msdb.dbo.sysjobs job
JOIN msdb.dbo.syslogins owner ON job.owner_sid = owner.sid
Displaying SQL Server Agent Jobs with Daily Schedules, Weekly Schedules, Monthly Schedules:
To report job name and schedule name we can use the following query :
SELECT j.name AS job_name, s.name AS schedule_name,
CAST(CONVERT(VARCHAR(8), js.next_run_date, 112) AS DATE) as next_run_date,
CAST(STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(6), next_run_time), 6), 5, 0, ':'), 3, 0, ':') AS TIME) AS next_run_time
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobschedules js ON j.job_id = js.job_id
INNER JOIN msdb.dbo.sysschedules s ON js.schedule_id = s.schedule_id
WHERE s.freq_type IN (4,8,16)
-- 4 -- Daily Schedule
-- 8 -- Weekly Schedule
--16 -- Monthly Schedule
This query retrieves the name of the SQL Agent job, the name of the schedule associated with the job, and the next scheduled run time for jobs with a daily schedule. The msdb.dbo.sysjobschedules system table contains information about the schedules associated with each job, and the msdb.dbo.sysschedules system table contains information about the schedules themselves.
The WHERE clause filters the results to only include jobs with a daily schedule, which is identified by a freq_type value of 4. You can modify the query to filter by other schedule frequencies or to retrieve additional information about the jobs, such as the last run time, duration, or outcome.
Note that the next_run_datetime column concatenates the next_run_date and next_run_time columns to create a single datetime value, which can be useful for sorting or filtering the results by scheduled run time. Additionally, the datetime value is cast to DATETIME data type to display the value in a human-readable format.
Find Avg Time Of Job Runs
To find the average runtime for all SQL Agent jobs using the run_duration column of the msdb.dbo.sysjobhistory table, you can use the AVG function to calculate the average duration in seconds, and then convert it to minutes using the formula AVG(run_duration)/60.0 AS AvgDurationInMinutes. Here's an example query:
SELECT j.name AS JobName, AVG(run_duration)/60.0 AS AvgDurationInMinutes
FROM msdb.dbo.sysjobhistory AS h
INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
WHERE h.step_id = 0
GROUP BY j.name
ORDER BY j.name;
This query calculates the average duration for each job in seconds using the AVG function, and then converts it to minutes by dividing by 60. The results are grouped by the job name using the GROUP BY clause, and sorted alphabetically by job name using the ORDER BY clause. The WHERE clause filters out job steps, so that only job-level executions are included.
Find Avg Time Of Job Runs +10
To find currently running SQL Agent jobs that have been running longer than the average runtime, by more than 10 minutes. You can join the msdb.dbo.sysjobactivity table with the results of the previous query, and filter for rows where the run_duration is greater than the AvgDurationInMinutes. The following script is a good example:
With JobTime as (
SELECT j.name AS JobName,
SUM(DATEDIFF(MINUTE, s.start_execution_date, GETDATE()) / 60) AS ElapsedMinutes,
AVG(h.run_duration)/60.0 AS AvgDurationInMinutes
FROM msdb..sysjobactivity AS s
INNER JOIN msdb..sysjobs AS j
ON s.job_id = j.job_id
Inner Join msdb..sysjobhistory as h
ON h.job_id = j.job_id
WHERE s.stop_execution_date IS NULL
GROUP BY j.name
)
Select JobName,ElapsedMinutes,AvgDurationInMinutes
From JobTime
Where ElapsedMinutes > AvgDurationInMinutes +10
Find Jobs That did NOT run today
To find jobs that did not run today in SQL Server Agent, you can use the msdb.dbo.sysjobhistory system table to get a list of jobs that have run in the past, and then filter out any jobs that have run today.
Here's an example query that returns the jobs that did not run today:
SELECT j.name AS job_name
FROM msdb.dbo.sysjobs j
WHERE NOT EXISTS (
SELECT 1
FROM msdb.dbo.sysjobhistory h
WHERE j.job_id = h.job_id
AND CONVERT(DATE, GETDATE()) = CONVERT(DATE, h.run_date)
)This query uses a subquery in the WHERE clause to filter out any jobs that have run today. The subquery joins the sysjobs and sysjobhistory tables on the job_id column and compares the run_date column to the current date using the CONVERT() function.
The main query selects the name column from the sysjobs table for all jobs that are not returned by the subquery.
This query should return a list of all jobs that did not run today. Note that the query only checks for jobs that have run in the past, so any new jobs that have never been executed will not be included in the result set.
Opening The SQL Server Agent node To View Operators
This query returns the name of each operator along with their email address and pager address, if available.
To use an operator in a notification, you must first create a SQL Server Agent alert that is triggered by a specific event, such as a job failure or a performance threshold being exceeded. You can then configure the alert to use one or more operators to receive the notification.
For example, the following SQL code creates an alert that is triggered when a SQL Server Agent job fails, and sends a notification to the operator named "DBA Team" or "Database Administrators"
USE msdb;
GO
EXEC dbo.sp_add_alert
@name = N'Job Failure',
@message_id = 0,
@severity = 0,
@enabled = 1,
@delay_between_responses = 0,
@include_event_description_in = 1,
@category_name = N'[Uncategorized]',
@job_name = N'Job1';
EXEC dbo.sp_add_notification
@alert_name = N'Job Failure',
@operator_name = N'DBA Team',
@notification_method = 1;
In this example, the sp_add_alert stored procedure creates an alert named "Job Failure" that is triggered when the job named "Job1" fails. The sp_add_notification stored procedure then adds the "DBA Team" operator as a recipient of the alert using the email notification method (1).
Purge Job History
MSDB.DBO.SP_PURGE_JOBHISTORY is a stored procedure in SQL Server that is used to delete old job history entries from the msdb.dbo.sysjobhistory table, which stores the history of job executions by SQL Server Agent. This stored procedure is useful for managing the size of the msdb database and improving query performance.
Here is an example of how to use MSDB.DBO.SP_PURGE_JOBHISTORY:
EXEC msdb.dbo.sp_purge_jobhistory @job_name = 'MyJob', @oldest_date = '2022-01-01'This statement purges all job history entries for the SQL Server Agent job named "MyJob" that are older than January 1st, 2022. You can customize the @job_name and @oldest_date parameters to specify the job and date range for purging job history entries.
Here are the parameters for MSDB.DBO.SP_PURGE_JOBHISTORY:
@job_name: Specifies the name of the SQL Server Agent job for which to purge job history entries. This parameter is optional, and if omitted, all job history entries that match the other criteria are purged.
@job_id: Specifies the ID of the SQL Server Agent job for which to purge job history entries. This parameter is optional, and if omitted, all job history entries that match the other criteria are purged.
@oldest_date: Specifies the oldest date for which to keep job history entries. Job history entries that have an end time before this date are deleted. This parameter is required.
@oldest_hours: Specifies the number of hours for which to keep job history entries in addition to the @oldest_date. Job history entries that have an end time before this time period are deleted. This parameter is optional.
@oldest_job: Specifies the number of job history entries to keep for each job. Job history entries for each job that exceed this limit are deleted. This parameter is optional.
You can use the MSDB.DBO.SP_PURGE_JOBHISTORY stored procedure in a SQL Server Agent job or as a scheduled task to automatically purge old job history entries at regular intervals. Note that you should carefully consider the data retention requirements of your organization and the impact of purging job history entries on auditing and troubleshooting.