SQL Server Agent - Job
Here are the few things which DBA should know about Agent jobs and consider when they deploy new SQL Server Agent Jobs. Here are some items to consider:
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:
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:
*** 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:
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.
Here are the few things which DBA should know about Agent jobs and consider when they deploy new SQL Server Agent Jobs. Here are some items to consider:
- Naming conventions
- 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
- These could also include a subset of jobs with these secondary prefixes:
- 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.
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.
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.
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.
No comments:
Post a Comment