As a starting point for a mini project to have all the SQL Agent jobs on servers we support documented both to determine what they do and what actions we should take in the event of failure, I want to know which jobs are obsolete.
This script shows the last successful run date and time plus how long it took for each job step, whether it was successful, which database it queries, the location of any log files, the command run by each job step, the created and last modified date and the owner of the job.
USE MSDB
GO
SELECT J.Name AS JobName,
JS.Step_Name AS StepName,
CAST(CAST(NULLIF(Last_Run_Date,0) AS VARCHAR(8)) AS DATETIME)
+ STUFF(STUFF(RIGHT('00000' + CAST(Last_Run_Time AS VARCHAR(6)),6),3,0,':'),6,0,':') AS Last_Run_DateTime,
JS.Last_Run_Duration,
CASE WHEN (JS.Last_Run_Outcome = 1) THEN 'Succeeded' ELSE 'Failed' END Last_Run_Status,
JS.Database_Name,
JS.SubSystem,
JS.Command AS Command,
JS.Output_File_Name,
J.Date_Created,
J.Date_Modified,
SUSER_NAME(J.Owner_SID) AS Job_Owner
FROM SysJobs J
INNER JOIN SysJobSteps JS ON J.Job_ID = JS.Job_ID
--WHERE JS.SubSystem = 'SSIS'
With the addition of the WHERE constraint to only return jobs which use the SSIS sub-system you can determine which jobs are using SSIS packages stored either within a file structure on within MSDB.
I plan to use this to remove all obsolete jobs and packages from our servers prior to a move to new hardware later in the year.
Nothing is going on our new servers unless it's written up in our SQL Agent documentation which includes actions to take in the event of failure, any ports to be opened for SFTP tasks and crucially who owns the job both from the techie side and the business. Ownership is key to making sure SQL Agent jobs are maintained.
