how to find sql server agent jobs related to a database

Solutions on MaxInterview for how to find sql server agent jobs related to a database by the best coders in the world

showing results for - "how to find sql server agent jobs related to a database"
Alessandra
03 Oct 2018
1-- list of jobs; selected info about jobs
2SELECT 
3 job_id
4,name
5,enabled
6,date_created
7,date_modified
8FROM msdb.dbo.sysjobs
9ORDER BY date_created
10
Marta
10 Aug 2020
1SELECT
2    [sJOB].[job_id] AS [JobID]
3    , [sJOB].[name] AS [JobName]
4    , [sJSTP].[step_uid] AS [StepID]
5    , [sJSTP].[step_id] AS [StepNo]
6    , [sJSTP].[step_name] AS [StepName]
7    , CASE [sJSTP].[subsystem]
8        WHEN 'ActiveScripting' THEN 'ActiveX Script'
9        WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
10        WHEN 'PowerShell' THEN 'PowerShell'
11        WHEN 'Distribution' THEN 'Replication Distributor'
12        WHEN 'Merge' THEN 'Replication Merge'
13        WHEN 'QueueReader' THEN 'Replication Queue Reader'
14        WHEN 'Snapshot' THEN 'Replication Snapshot'
15        WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
16        WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
17        WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
18        WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
19        WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
20        ELSE sJSTP.subsystem
21      END AS [StepType]
22    , [sPROX].[name] AS [RunAs]
23    , [sJSTP].[database_name] AS [Database]
24    , [sJSTP].[command] AS [ExecutableCommand]
25    , CASE [sJSTP].[on_success_action]
26        WHEN 1 THEN 'Quit the job reporting success'
27        WHEN 2 THEN 'Quit the job reporting failure'
28        WHEN 3 THEN 'Go to the next step'
29        WHEN 4 THEN 'Go to Step: ' 
30                    + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3))) 
31                    + ' ' 
32                    + [sOSSTP].[step_name]
33      END AS [OnSuccessAction]
34    , [sJSTP].[retry_attempts] AS [RetryAttempts]
35    , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]
36    , CASE [sJSTP].[on_fail_action]
37        WHEN 1 THEN 'Quit the job reporting success'
38        WHEN 2 THEN 'Quit the job reporting failure'
39        WHEN 3 THEN 'Go to the next step'
40        WHEN 4 THEN 'Go to Step: ' 
41                    + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3))) 
42                    + ' ' 
43                    + [sOFSTP].[step_name]
44      END AS [OnFailureAction]
45FROM
46    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
47    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
48        ON [sJSTP].[job_id] = [sJOB].[job_id]
49    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
50        ON [sJSTP].[job_id] = [sOSSTP].[job_id]
51        AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
52    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
53        ON [sJSTP].[job_id] = [sOFSTP].[job_id]
54        AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
55    LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]
56        ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
57WHERE [sJSTP].[command] LIKE '%MyStoredProc%'
58ORDER BY [JobName], [StepNo]