SQL Server Agent Job Management
At
our organization we have been using SQL Server Agent since SQL Server 2000. We typically setup the Job and that
is about it. If we need to make a change we do that and do not think twice
about it. On some of our servers we have hundreds of jobs, so in this
circumstance, we have lost track of what many of them do. Recently, we have
been getting asked a number of questions from our management and Development
team about when changes were made, scheduling options, dependencies, etc. Can
you give me any insight into how to best leverage SQL Server Agent?
Solution
SQL Server Agent has been critical to the SQL Server installations I have
worked on since the SQL Server 6.5 days. Much of the core functionality remains
the same in SQL Server Agent, but new features have been added over the years
which every DBA should know about and consider when they deploy new SQL Server
Agent Jobs. Here are some items to consider:
- Naming conventions
- Custom Categories
- Job Notes
- Schedules
- Dependencies
Naming Conventions
When
it comes to naming conventions, I have only seen a few companies that have put
any thought into how they name their SQL Server Agent Jobs. For the most part
the job names are logical for the task they are completing, but not grouped by
name or category. DBAs end up scanning the jobs to find what they are looking
for rather than being able to look at a subset of SQL Server Agent Jobs quickly
and find the correct one. If you have 50 or more jobs, using standard prefixes
or custom categories (see below) should help that process.
As
such, I recommend creating logical prefixes for your SQL Server Agent Jobs.
Some of these prefixes could be:
- "Admin" or "DBA" - Prefix for DBA
related Jobs
- These could also
include a subset of jobs with these secondary prefixes:
- "Maint",
"Backup", "Restore", "SSIS", etc.
- Another aspect to job
names that is important to me is the frequency. These prefixes could be:
- Hourly, Daily, Weekly,
Monthly, Ad-Hoc, Month-End Process, etc.
- If you pull all of this
together a few examples could be:
- Admin - Maint - Weekly
- Index Rebuilds
- Admin - Backup - Daily
- System and User Defined Databases
- Admin - SSIS - Hourly
- Product Catalog Import
- Application Name - Prefix for jobs related to an
application
- For example,
"Siebel", "CRM", "Manufacturing", etc.,
these too could include the frequency or other beneficial categorizations
to your organizations.
- Business Process - Prefix for jobs related to a
business process
- For example,
"Revenue Reports", "External Reports",
"Dashboard Refresh", etc., these too could include the
frequency or other beneficial categorizations to your organizations.
Another
aspect of naming conventions that I have seen benefit one organization is
adding a prefix "zDisabledToDelete" with the date for deletion to
indicate when the job should be deleted. This gives you the benefit of sorting
the job at the end of the list and knowing when the job is intended to be
deleted. For example, a disabled job could be named
"zDisabledToDelete_12312010_Admin-SSIS-Daily-Export".
Custom Categories
Along
the same lines of naming conventions is custom job categories. In some
organizations, they organize their jobs by custom category rather than by name.
Both of these techniques are not mutually exclusive. You could have a custom
category setup along with a naming convention. In either circumstance, select a
process, be consistent and stick with it.
For
information on how to setup a custom category in SQL Server 2000 and 2008,
check out this tip from Tim Ford:
- Custom job categories to organize your
SQL Agent jobs
Job Notes
Job
notes. What job notes? Did you know you have the option to record job notes for
each of your SQL Server Agent Jobs? Unfortunately, I see more Jobs with blank
job notes than anything meaningful.
In
SQL Server 2000, the job notes section was short to say the least. With SQL
Server 2005 and 2008, you now have a much larger text box to store relevant and
important job notes. Why don't you take advantage of the opportunity and
include this type of information:
- Description
- Start Date
- General Schedule
- Revision History
- Just a simple note with
who, when and what was changed in the job will garner applause from me.
- Point of Contact
- IT
- Business Unit
- Dependencies
- Jobs, Databases, SSIS
Packages, XML feeds, FTP sites, etc.
Here
is a screen shot of a sample set of job notes in SQL Server 2008:
***
NOTE *** - Press CTRL + ENTER to insert blank lines in the Description
interface shown above.
Depending
on the job type, you also have the ability to record notes in the individual
job steps. For Transact-SQL script (T-SQL) you can use two dashes
("--") to comment one line of code or use a forward slash followed by
one or more asterisks in conjunction with one or more asterisks followed by a
forward slash i.e. (/*** Code here ***/) to comment out more than one line of
code.
With
these options to record notes in your SQL Server Agent Jobs, please take
advantage of them for the following reasons:
- Have a general history for the job without having to
check production change logs.
- Know who to get in touch with if a job fails without
having to send a mass email or individually ask team members.
- Ability to have multiple team members troubleshoot job
issues.
Schedules
I
have the unfortunate story to share where I found a SQL Server with hundreds of
duplicate jobs, one for each day of the week, database, etc. For example, there
were seven full backup jobs, one for each day of the week.
There were import and
export processes that also
had one job per day. Another set of maintenance jobs for each type of maintenance (DBCC CHECKDB, UPDATE STATISTICS, DBCC UPDATEUSAGE, etc.)
times the number of databases. The folks at this organization did not know any
better and were trying to do the right thing. They just had a lot of SQL Server
Agent Jobs.
With
this being said, one job can have multiple schedules. For example, one job can
run at 8:00 AM, 10:00 AM and 5:00 PM. This is accomplished with separate
schedules, see below.
Check
out all of the scheduling options (one time, daily, weekly, monthly, hourly,
one time a day, etc.) before you schedule your next job to ensure the least
number of schedules are created.
Dependencies
Another
item I have run into is a complex set of jobs that need to run across a number
of different SQL Server instances in a serial manner. In this circumstance, the
jobs were setup to run based on time delays assuming the previous job completed
successfully. This worked for the company for a period of time because there
were only a dozen or so jobs and there was plenty of time to complete the jobs
with 15 minute gaps. At times jobs would fail or run over the 15 minute grace
period, so they would troubleshoot it and move on.
As
there business grew, so did the data and the corresponding complexity. They
also began losing processing time to meet business needs in additional time zones.
So the processing windows shrunk quickly and trying to manage more jobs became
even more complex.
In
this circumstance, be sure to check out the msdb.dbo.sp_start_job system stored procedure in conjunction with Linked Servers. With a properly configured Linked Server,
you can call the msdb.dbo.sp_start_job system stored procedure across the
Linked Server as the final job step of the current job to start the next job.
This should help with running a process when the preceding job failed and also
prevent trying to time balance the jobs to prevent overlap.
Next Steps
- Evaluate your current processes to manage your SQL
Server Agent Jobs.
- Consider some of the options outlined in this tip and
build a set of processes that make sense for your organization.
- Once you have your processes in place, work through the
process one SQL Server instance at a time to ensure all of the jobs meet
your standard.
- Do you have other SQL Server Agent best practices you
follow? Please let us know in the comments section (see above) for this
tip.
- Stay tuned for some additional SQL Server Agent tips to
help you benefit from implementing one or more of the processes listed in
this tip.
- Until then, check out the published SQL Server Agent tips. They are a wealth of information to get you up to
speed on SQL Server Agent.