How to find SQL Server agent jobs related to a database

When migrating a database to a new SQL Server instance, there are numerous steps to be taken, as I mention here.

One of the considerations is to move all Agent Jobs related to the database to the new instance.

How do we find which jobs to move, though?

Option 1: ask the system owner (or the owner of the database, or the developer of the database)

If this fails…

Option 2: check manually every SQL Server Agent job on the instance.

If this fails…

Option 3: use the script below. The script searches through the jobs steps for the name of the database you have entered in the variable. The script is not optimal, because if the job, for example, is executing a SSIS package, which internally has a reference to the database, then this script will not acknowledge that. In this case, go back to steps 1 and 2.

*** This script is a modification of the SSMS scripts used to get the job information, but it works. :)

?Download download.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
DECLARE @databaseName SYSNAME
SET @databaseName = 'name of the database here'
CREATE TABLE #tmp_sp_help_jobstep
    (
      step_id INT NULL ,
      step_name NVARCHAR(128) NULL ,
      subsystem NVARCHAR(128) COLLATE Latin1_General_CI_AS
                              NULL ,
      command NVARCHAR(MAX) NULL ,
      flags INT NULL ,
      cmdexec_success_code INT NULL ,
      on_success_action TINYINT NULL ,
      on_success_step_id INT NULL ,
      on_fail_action TINYINT NULL ,
      on_fail_step_id INT NULL ,
      server NVARCHAR(128) NULL ,
      database_name SYSNAME NULL ,
      database_user_name SYSNAME NULL ,
      retry_attempts INT NULL ,
      retry_interval INT NULL ,
      os_run_priority INT NULL ,
      output_file_name NVARCHAR(300) NULL ,
      last_run_outcome INT NULL ,
      last_run_duration INT NULL ,
      last_run_retries INT NULL ,
      last_run_date INT NULL ,
      last_run_time INT NULL ,
      proxy_id INT NULL ,
      job_id UNIQUEIDENTIFIER NULL
    )
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE crs CURSOR local fast_forward
FOR
    ( SELECT    sv.job_id AS [JobID]
      FROM      msdb.dbo.sysjobs_view AS sv
    )
OPEN crs
FETCH crs INTO @job_id
WHILE @@fetch_status >= 0 
    BEGIN
        INSERT  INTO #tmp_sp_help_jobstep
                ( step_id ,
                  step_name ,
                  subsystem ,
                  command ,
                  flags ,
                  cmdexec_success_code ,
                  on_success_action ,
                  on_success_step_id ,
                  on_fail_action ,
                  on_fail_step_id ,
                  server ,
                  database_name ,
                  database_user_name ,
                  retry_attempts ,
                  retry_interval ,
                  os_run_priority ,
                  output_file_name ,
                  last_run_outcome ,
                  last_run_duration ,
                  last_run_retries ,
                  last_run_date ,
                  last_run_time ,
                  proxy_id
                )
                EXEC msdb.dbo.sp_help_jobstep @job_id = @job_id
        UPDATE  #tmp_sp_help_jobstep
        SET     job_id = @job_id
        WHERE   job_id IS NULL
        FETCH crs INTO @job_id
    END
CLOSE crs
DEALLOCATE crs
CREATE TABLE #tmp_sp_help_proxy
    (
      proxy_id INT NULL ,
      name NVARCHAR(300) NULL ,
      credential_identity NVARCHAR(300) NULL ,
      enabled TINYINT NULL ,
      description NVARCHAR(MAX) NULL ,
      user_sid BINARY(200) NULL ,
      credential_id INT NULL ,
      credential_identity_exists INT NULL
    )
INSERT  INTO #tmp_sp_help_proxy
        ( proxy_id ,
          name ,
          credential_identity ,
          enabled ,
          description ,
          user_sid ,
          credential_id ,
          credential_identity_exists
        )
        EXEC msdb.dbo.sp_help_proxy
SELECT  tshj.step_id AS [ID] ,
        tshj.step_name AS [Name] ,
        ISNULL(tshj.command, N'') AS [Command] ,
        tshj.cmdexec_success_code AS [CommandExecutionSuccessCode] ,
        ISNULL(tshj.database_name, N'') AS [DatabaseName] ,
        ISNULL(tshj.database_user_name, N'') AS [DatabaseUserName] ,
        tshj.flags AS [JobStepFlags] ,
        NULL AS [LastRunDate] ,
        tshj.last_run_duration AS [LastRunDuration] ,
        tshj.last_run_outcome AS [LastRunOutcome] ,
        tshj.last_run_retries AS [LastRunRetries] ,
        tshj.on_fail_action AS [OnFailAction] ,
        tshj.on_fail_step_id AS [OnFailStep] ,
        tshj.on_success_action AS [OnSuccessAction] ,
        tshj.on_success_step_id AS [OnSuccessStep] ,
        tshj.os_run_priority AS [OSRunPriority] ,
        ISNULL(tshj.output_file_name, N'') AS [OutputFileName] ,
        tshj.retry_attempts AS [RetryAttempts] ,
        tshj.retry_interval AS [RetryInterval] ,
        ISNULL(tshj.server, N'') AS [Server] ,
        CASE LOWER(tshj.subsystem)
          WHEN 'tsql' THEN 1
          WHEN 'activescripting' THEN 2
          WHEN 'cmdexec' THEN 3
          WHEN 'snapshot' THEN 4
          WHEN 'logreader' THEN 5
          WHEN 'distribution' THEN 6
          WHEN 'merge' THEN 7
          WHEN 'queuereader' THEN 8
          WHEN 'analysisquery' THEN 9
          WHEN 'analysiscommand' THEN 10
          WHEN 'dts' THEN 11
          WHEN 'ssis' THEN 11
          WHEN 'powershell' THEN 12
          ELSE 0
        END AS [SubSystem] ,
        ISNULL(sp.name, N'') AS [ProxyName] ,
        tshj.last_run_date AS [LastRunDateInt] ,
        tshj.last_run_time AS [LastRunTimeInt]
FROM    msdb.dbo.sysjobs_view AS sv
        INNER JOIN #tmp_sp_help_jobstep AS tshj ON tshj.job_id = sv.job_id
        LEFT OUTER JOIN #tmp_sp_help_proxy AS sp ON sp.proxy_id = tshj.proxy_id
WHERE   tshj.database_name = @databaseName
        OR tshj.command LIKE '%' + @databaseName + '%'
ORDER BY [ID] ASC
DROP TABLE #tmp_sp_help_jobstep
DROP TABLE #tmp_sp_help_proxy

 

2 comments to How to find SQL Server agent jobs related to a database