We're giving away another Kindle loaded with SQL Server eBooks

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

In addition, you can setup one job to run on multiple days at the same time in one job schedule.  See the screen shot below as a point of reference.

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.

• Custom sp_start_job to delay next task until SQL Agent Job has completed
• Running a SSIS Package from SQL Server Agent Using a Proxy Account
• SQL Server Agent Job Management Scripts
• Reassign Job Ownership for All Jobs on All SQL Servers
• Enable and Disable SQL Server Agent Jobs for Maintenance Mode
• More...

select job_id, name, step_id, step_name, subsystem, command, database_name,

       last_run_outcome, last_run_date,

       (substring(last_run_time, 1, 2) + ':' + substring(last_run_time, 3, 2) + ':' + substring(last_run_time, 5, 2)) as last_run_time,

       (substring(last_run_duration, 1, 2) + ':' + substring(last_run_duration, 3, 2) + ':' + substring(last_run_duration, 5, 2)) as last_run_duration,

       last_run_retries,

       sMessage

from (

       select a.job_id, name, step_id, step_name, subsystem, command, database_name,

             (case last_run_outcome when 0 then '실패'

                           when 1 then '성공' else '알수없음' end) as last_run_outcome,

             last_run_date,

             right('S000000' + convert(varchar(8), last_run_time), 6) as last_run_time,

             right('S000000' + convert(varchar(8), last_run_duration), 6) as last_run_duration,

             last_run_retries,

             (case when last_run_outcome = 0 then (

                    select top 1 [message]

                                 from msdb.dbo.sysjobhistory

                           where job_id = a.job_id and run_date = b.last_run_date and run_time = b.last_run_time

             ) else '' end) as sMessage

       from msdb.dbo.sysjobs a

             left join msdb.dbo.sysjobsteps b on a.job_id = b.job_id

       where enabled = 1

) as O

order by name, step_id