SQL Server Agent Job Management Scripts

Solution

Since we will probably be dealing with existing jobs, these are the basic changes we want to implement:

• Naming conventions for scheduled jobs
• Naming conventions for job steps
• Adding Job Categories
• Adding Job Descriptions

With the help of several targeted scripts, we can work on a test server to implement the following steps to get a suitable set of scripts.

1. Create sample jobs and steps
2. Execute Jobs to verify the functionality
3. Generate and execute code for job names modification, job categories and job description
4. Generate and execute code for job steps name modification
5. Test functionality with changes made

When performing the task on a production server steps 1 and 2 would be skipped. Also, if you have some scheduled jobs already present on the test server that you can work with, then steps 1 and 2 may be skipped as well.

---

Create Sample Jobs and Steps

For simplicity, we will create a few jobs without a schedule. The job steps only have a SELECT statement to execute. The following script will create the jobs and add to the server. You may create as many jobs as you want by just replacing job names at the required places in the below script. I will create three jobs named Job1, Job2 and Job3.

--Script #1: Create sample jobs and steps on server

USE msdb
GO

-- Create Job
EXEC dbo.sp_add_job @job_name = N'Job1'
GO

-- Create Job Step1 and Step2
EXEC sp_add_jobstep
@job_name = N'Job1',
@step_name = N'Step1',
@on_success_action= 3,
@command = 'select ''Job step completed'''

EXEC sp_add_jobstep
@job_name = N'Job1',
@step_name = N'Step2',
@on_success_action= 1,
@command = 'select ''Job step completed'''
GO

-- Add job to Server
EXEC dbo.sp_add_jobserver
@job_name = N'Job1',
@server_name = N'Provide Server name here'
GO

I have created three jobs Job1, Job2 and Job3 with 2 steps in each. No schedule has been configured and jobs would need to be executed manually. After creating the jobs and steps, we may verify using SSMS or the following script.

--Script #2: Verify the created jobs and steps

USE [msdb]
GO

SELECT name, step_name
FROM dbo.sysjobs INNER JOIN dbo.sysjobsteps
ON dbo.sysjobs.job_id = dbo.sysjobsteps.job_id
GO

---

Execute Jobs to Verify Functionality

It would be good to execute and test the sample jobs. We may use SSMS or the following T-SQL for execution of the created jobs. I have used the following T-SQL commands for execution of the three jobs

--Script #3: Execute Jobs

USE [msdb]
GO

EXEC dbo.sp_start_job @job_name = 'Job1'
EXEC dbo.sp_start_job @job_name = 'Job2'
EXEC dbo.sp_start_job @job_name = 'Job3'
GO

You can also verify the execution through the following script.

--Script #4: verify the successful execution

USE [msdb]
GO

SELECT j.name JobName,h.step_name StepName,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as ExecutionStatus
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id AND step_name <> '(Job outcome)'
ORDER BY runtime DESC, j.name
GO

---

Generate and Execute Code for Job Name Modification, Job Category and Job Description

Now we can try our modification scripts on the created jobs. First, we will generate code for changing job steps using a system stored procedure.

--Script #5: Generate commands to change job names

USE msdb
GO

SELECT 'EXEC dbo.sp_update_job
@job_name = '''+name+''',
@new_name = ''New Name Here'',
@description = ''Description Here'',
@category_name = ''Description Here'''
FROM sysjobs
GO

As a result from runing the above commands, statements will be generated for changing the name, adding a job description and categories as shown in script #6 below. Paste these commands in a SSMS query pane and take time to analyze and provide meaningful names, descriptions and categories.

It is important to note that SQL Server allows only predefined categories to be used. Read this tip to learn more about Job Categories and also how to add your own categories. By following the steps mentioned in the tip, I have created a new category "MSSQLTips sample jobs" to be used. If you already have a suitable predefined category then it may be used here.

--Script #6: Provide parameters and execute statements

EXEC dbo.sp_update_job @job_name = 'Job1', @new_name = 'Sample Job 1', @description = 'Created for MSSQL tip', @category_name = 'MSSQLTips sample jobs'

EXEC dbo.sp_update_job @job_name = 'Job2', @new_name = 'Sample Job 2', @description = 'Created for MSSQL tip', @category_name = 'MSSQLTips sample jobs'

EXEC dbo.sp_update_job @job_name = 'Job3', @new_name = 'Sample Job 3', @description = 'Created for MSSQL tip', @category_name = 'MSSQLTips sample jobs'
GO

To confirm the changes run sp_help_job system stored procedure in MSDB database. Below I can see the changes were made.

---

Generate and Execute Code for Job Step Name Modification

Now we can move to changing job step names. SQL Server provides no system stored procedure for this purpose. Hence we will create our own stored procedure for this purpose. This procedure takes three parameters Job Name, Current Step Name and New Step Name.

Script #7: Generate commands for step name change

USE msdb
GO

CREATE PROCEDURE USP_UpdateJobSteps
(@JobName varchar(256), @StepName varchar(256), @NewName varchar(256))
AS
update dbo.sysjobsteps
set step_name = @NewName
FROM dbo.sysjobsteps INNER JOIN dbo.sysjobs
ON dbo.sysjobsteps.job_id = dbo.sysjobs.job_id
where step_name = @StepName
and dbo.sysjobs.name = @JobName
GO

If we want to generate the commands to change the job step names to be consistent we can execute the following code or use a WHERE clause to limit the jobs we want to change. This then creates the output that can be used in Script #9.

--Script #8: Generate commands for steps name change

USE msdb
GO

SELECT 'EXEC USP_UpdateJobSteps '''+name+' '', '''+step_name+''', ''Provide New Name Here'''
FROM dbo.sysjobs INNER JOIN dbo.sysjobsteps
ON dbo.sysjobs.job_id = dbo.sysjobsteps.job_id

The above command generated this script with job names, old step names and we can provide anew step name as follows:

--Script #9: Update job step names

USE msdb
GO

EXEC USP_UpdateJobSteps 'Sample Job 1 ', 'Step1', 'Step 1 of Sample Job 1'
EXEC USP_UpdateJobSteps 'Sample Job 1 ', 'Step2', 'Step 2 of Sample Job 1'
EXEC USP_UpdateJobSteps 'Sample Job 2 ', 'Step1', 'Step 1 of Sample Job 2'
EXEC USP_UpdateJobSteps 'Sample Job 2 ', 'Step2', 'Step 2 of Sample Job 2'
EXEC USP_UpdateJobSteps 'Sample Job 3 ', 'Step1', 'Step 1 of Sample Job 3'
EXEC USP_UpdateJobSteps 'Sample Job 3 ', 'Step2', 'Step 2 of Sample Job 3'
GO

---

Test and cleanup

Required changes are now implemented on Jobs and Steps. This can be verified by using script #2. Now we may test the functionality of jobs to make sure that modifications have made no impact on functionality of the jobs. Use script #3 (do not forget to provide updated job names) and script #4 to execute jobs and verify output respectively.

At this point if we want to drop the created stored procedure we can do this by using the drop command (DROP PROCEDURE USP_UpdateJobSteps).