CODEWITHSUNDEEP
sql-servert-sqlconfigurationautomationwindows-server-2012
S.Zsolt
S.Zsolt

Reputation: 23

How to mass update SQL Server Agent Job retry attempts based on Job name

I'm relatively new to running SQL servers and need some help with a task I just got.

I need to update a LOT of SQL Agent Jobs, but not all of them. I need to update only ones which have a specific word in their name (like '%Test%'). I need to set 2 things in each of them: retry attempts (to 2) and retry intervals (to 10 min).

Because this is a production server, I cannot really install any third-party program or restart the server, or anything like that. I can only run queries/stored procedures.

The server is running Windows Server 2012 Standard 6.2 and Microsoft SQL Server 2016 13.0.5366.0. I use TSQL and SSMS.

Any help would be appreciated!

Edit:

Problem is solved. With the combination of a cursor and stored procedure sp_update_jobstep. Thank you all for your help!

Code I wrote for this:

USE msdb ;  

DECLARE @jobname nvarchar (max);

DECLARE cursor_asd CURSOR
FOR SELECT 
  jb.name
FROM msdb.dbo.sysjobs jb
WHERE name like '%Delta%';

OPEN cursor_asd;

FETCH NEXT FROM cursor_asd INTO
    @jobname;

WHILE @@FETCH_STATUS = 0
    BEGIN

        Print @jobname
         EXEC dbo.sp_update_jobstep  
            @job_name = @jobname,  
            @step_id = 1,  
            @retry_attempts = 2,
            @retry_interval = 5 ;  

        FETCH NEXT FROM cursor_asd INTO
            @jobname;
    END;

CLOSE cursor_asd;
DEALLOCATE cursor_asd;

Upvotes: 2

Views: 2375

Answers (3)

Tim Mylott
Tim Mylott

Reputation: 2723

Retry attempts for a SQL Agent Job are defined at each individual job step. If you have jobs with multiple steps you'll have to determine which step you're wanting to update unless you're just setting the same retry on all steps.

There are basically two tables that would be in play here:

Since retry is defined at each individual step you'll be looking at the following columns in [sysjobsteps]:

  • retry_attempts - Number of retry attempts made if the step fails.
  • retry_interval - Amount of time, in minutes, to wait between retry attempts.

Here is a simple query which shows how you can join those two tables and filter by job name:

SELECT     [jbs].* --I'm just returning the job step columns here as an example
FROM       [msdb].[dbo].[sysjobs] [jb]
INNER JOIN [msdb].[dbo].[sysjobsteps] [jbs]
    ON [jbs].[job_id] = [jb].[job_id]
WHERE      [jb].[name] LIKE '%Test%' --Job Name Filter
           AND [jbs].[step_name] = 'JobStepName'; --Use this if needing to filter to a specific job step.

We can then easily turn that into an update statement once we have it returning the correct job steps we want to update:

UPDATE     [jbs]
SET        [jbs].[retry_attempts] = '2'  --retry 2 times
         , [jbs].[retry_interval] = '10' --every 10 minutes
FROM       [msdb].[dbo].[sysjobs] [jb]
INNER JOIN [msdb].[dbo].[sysjobsteps] [jbs]
    ON [jbs].[job_id] = [jb].[job_id]
WHERE      [jb].[name] LIKE '%Test%' --Job Name Filter
           AND [jbs].[step_name] = 'JobStepName'; --Use this if needing to filter to a specific job step.

Upvotes: 2

J Sidhu
J Sidhu

Reputation: 677

Look into its documentation for

msdb.dbo.sp_update_jobstep

link

Upvotes: 0

Isaac
Isaac

Reputation: 3363

If you are on SQL Server 2016 or higher you could use sp_update_jobstep. Be very careful and test thoroughly with a few jobs before doing all of them.

Upvotes: 1

Related Questions