Find the SQL Server jobs scheduled in a server and its detailed analysis
Monitoring all the jobs in SQL Server Agent is quite a tough task. If we query them from MSDB database and pull the desired results by sorting as per issue it would be better for troubleshooting purpose.
Below Queries will help in troubleshooting the jobs in MSDB:
use msdb
SELECT [sJOB]
.[job_id] AS [JobID], [sJOB].[name] AS [JobName], [sDBP].[name] AS [JobOwner], [sCAT].[name] AS [JobCategory], [sJOB].[description] AS [JobDescription], CASE [sJOB].[enabled]WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS [IsEnabled], [sJOB].[date_created] AS [JobCreatedOn], [sJOB].[date_modified] AS [JobLastModifiedOn], [sSVR].[name] AS [OriginatingServerName], [sJSTP].[step_id] AS [JobStartStepNo], [sJSTP].[step_name] AS [JobStartStepName], CASE WHEN [sSCH].[schedule_uid] IS NULL THEN 'No' ELSE 'Yes'END AS [IsScheduled], [sSCH].[schedule_uid] AS [JobScheduleID], [sSCH].[name] AS [JobScheduleName], CASE [sJOB].[delete_level]WHEN 0 THEN 'Never'WHEN 1 THEN 'On Success'WHEN 2 THEN 'On Failure' WHEN 3 THEN 'On Completion'END AS [JobDeletionCriterion] FROM [msdb].[dbo].[sysjobs] AS [sJOB]LEFT JOIN [msdb].[sys].[servers] AS [sSVR]ON [sJOB].[originating_server_id] = [sSVR].[server_id]LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]ON [sJOB].[category_id] = [sCAT].[category_id]LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]ON [sJOB].[job_id] = [sJSTP].[job_id]AND [sJOB].[start_step_id] = [sJSTP].[step_id]LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]ON [sJOB].[owner_sid] = [sDBP].[sid]LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]ON [sJOB].[job_id] = [sJOBSCH].[job_id]LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id] ORDER BY [JobName]-----------------------------------------------------------------------------------
One more query which gives you some more information which is not provided in the above query
This is the best query for troubleshooting the jobs of MSDB
select msj.name [Job Name], Case when msj.enabled=1 then 'Yes' When msj.enabled=0 then 'No' End as [Job Enabled],suser_sname(msj.owner_sid) [Job Owner],msjs.last_run_date [Last Run Date], msjs.last_run_time [Last Run Time], msjs.last_run_duration [Run Duration],msjsc.next_run_date [Next Run Date],msjsc.next_run_time [Next Run Time],Case when msjs.last_run_outcome=1 Then 'Job Succeeded' when msjs.last_run_outcome=0 Then 'Job Failed' End as [Last Run Status],so.email_address [Email to sent],Case when msj.[notify_level_email]=1 Then 'When Job Succeeds' when msj.[notify_level_email]=2 Then 'When Job Fails' when msj.[notify_level_email]=3 Then 'When Job Completes' End as [Mail Reason] from msdb.dbo.sysjobs msj Inner join msdb
.dbo.sysjobservers msjs on msj.job_id=msjs.job_id Inner join msdb.dbo.sysjobschedules msjsc on msj.job_id=msjsc.job_id Left OUTER Join msdb.dbo.sysoperators SO on msj.notify_email_operator_id=SO.id-----------------------------------------------
Hope the above queries helps
Thanks
