SQL Server Agent – Job Naming

I have seen a number of SQL Server instances where multiple jobs have been created with no real thought for the job name. Especially in systems with lots of jobs, this causes a degree of confusion, as it becomes difficult to quickly identify which jobs are associated with which database. To simplify maintenance, use a naming standard.

My approach to naming SQL Agent Jobs is as follows

{DBName} {Job Description}

For example:

Payables Clear balance

Payables Update GL

Hopefully, this allows the DBA to identify which jobs to check in case of problems. There are a number of jobs that do not easily fall into this scheme. For example, jobs to backup the database or perform consistency checks. These administrative jobs are named as follows:

DB ADMIN {Description}

For example:

DB Admin Overnight Full Backup

DB Admin Weekly Consistency check

There are also jobs which update a number of databases. For example, an overnight process to post journal entries from the journal database to a general ledger database. In this case, I will use a system identifier for the database name – in this case I would name the job “Posting Overnight Journals”.

It is possible to categorize SQL Agent Jobs. In my experience, this is not used very often and categories are not as visible as the job names. There are a number of categories created by default when SQL Server is installed. In Sql Server Management Studio, expand SQL Server Agent, right-click on Jobs and select  ‘Manage Job Categories’. By adding a local job category, the DBA can group jobs. The ‘Manage Job Categories’ dialog allows the DBA to view jobs within a category. However, the DBA cannot use this dialog to edit jobs or display job properties.

Finally, use comments (in the Job Properties – Description Textbox) to indicate the Job purpose, and provide a brief business description. These comments are easily visible and provide a quick reminder for what the job does and who to contact if it fails. These comments should not replace proper documentation. In my case, I maintain a Wiki which describes Jobs, and includes details of what goes wrong (erro messages, who to contact and how the problem was resolved).