Problem
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.
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.
- 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.
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.
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
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.
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.
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.
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.
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.
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:
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).
Next Steps
- 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
