In a previous tip, the tip reviewed several steps for a better and improved environment for scheduled jobs. I am totally convinced with the recommendations and want to implement these for my scheduled jobs. To make this easier, I want to create a template so I can produce a suitable and reliable way of updates for my production server. In this tip I provide a step by step exercise to perform on a test server to prepare the customized and tested scripts to run on the production server.
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.
Create sample jobs and steps
Execute Jobs to verify the functionality
Generate and execute code for job names modification, job categories and job description
Generate and execute code for job steps name modification
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
-- 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
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
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:
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).
Keep your production server requirements noted, evolve the script through testing and modifications on your test server
Feel free to filter the jobs in the scripts to limit which jobs you are modifying
Jobs created through maintenance plans may also be modified
Click here to read a tip about analyzing SQL Server Agent Job History
Last Update: 11/9/2010
About the author
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.
I like your article, and agree that Categorizing Jobs and using Consistent naming conventions is underused and important. I don't understand why you need to create a stored procedure to execute the update command. Why not just script the update command similar to this...
SELECT 'UPDATE dbo.sysjobsteps
set step_name = ''NEWNAME''
where step_name = ''' + step_name + '''
and dbo.sysjobs.name = ''' + dbo.sysjobs.name + ''''
FROM dbo.sysjobsteps INNER JOIN dbo.sysjobs
ON dbo.sysjobsteps.job_id = dbo.sysjobs.job_id
Which would output the same, only as an update instead of an exec of another SP? One less step to troubleshoot.
Thanks for your article!
Friday, November 19, 2010 - 4:49:14 AM - Atif Shehzad
Thanks for appreciation. Step name may be updated by using direct update statement as you mentioned. But if there are many job steps (as expected) then generating full statement for each step name would not be as much readable as to use in form of stored procedure (as did in article). So creating stored procedure was just to make the statements more readable and easy to manage.