Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

SQL Server Agent Job Management Scripts

MSSQLTips author Atif Shehzad By:   |   Read Comments (2)   |   Related Tips: More > SQL Server Agent
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.

  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.

 confirm the changes by running sp_help_job system stored procedure in MSDB database


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).

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


Last Update: 11/9/2010


About the author
MSSQLTips author Atif Shehzad
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Thursday, November 18, 2010 - 10:11:06 AM - Jason Read The Tip
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... use msdb go 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 Read The Tip

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.

Thanks 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.