Tag Archives: schedule

How to get the date & time a job or schedule was modified in SQL Server

Database Administrator, dba, SQL Server, , , , , , , , , , , , ,

The script below has one parameter, @SD or start date. The logic of the script will return two tables showing the modified jobs and schedules from the start date onward.

By default the start date will be set to one year ago to the day which will return a full years worth of data up to the time the script is run. To enter a date manually change the line SET @SD = NULL; –‘20180101 00:00:00’; removing NULL; — and changing the date time text. 

USE msdb;
GO

DECLARE @SD AS DATETIME;

SET @SD = NULL; --'20180101 00:00:00';

IF @SD IS NULL
BEGIN
SET @SD = DATEADD(YEAR, - 1, GETDATE());
END;

SELECT @SD AS StartDate;

-- Jobs that have been modified
SELECT [Name] AS JobName
	,[Enabled]
	,[Date_created]
	,[Date_modified]
FROM sysjobs
WHERE [date_modified] > @SD
--AND enabled = 0
ORDER BY [date_modified] DESC;

-- Schedules that have been modified
SELECT [Name] AS ScheduleName
	,[Enabled]
	,[Date_created]
	,[Date_modified]
FROM sysschedules
WHERE [date_modified] > @SD
--AND enabled = 0
ORDER BY [date_modified] DESC;
GO