Did you ever wonder how SQL Server keeps track of the jobs created inside of the SQL Server Agent? No? Well, neither did I until I became a DBA and noticed just how easy it is for someone to create a job inside of SQL Agent. You might be saying “Tom, what’s wrong with that?” Lots of things, actually. Most importantly is that people will create jobs without your knowledge and then expect you to fix them when they break.
Believe me, you do NOT want to be woken up every night because of a job failing that you had no idea about and someone expects you to fix IMMEDIATELY. So, best to be aware about what you have now and if new jobs are added or current jobs are modified. And you do that by heading into the sysjobs table.
What is this table for?
This table will contain one row for each job you have defined inside of SQL Agent. It’s that simple.
Why should you care?
There are a handful of reasons you should care. The first one I would advocate is this: troubleshooting. Many times you are asked to investigate problems with jobs so it is helpful to know if any jobs have changed in the past week. You can get that info by running this:
SELECT * FROM msdb..sysjobs WHERE DATEDIFF(dd, date_modified, GETDATE()) < 7
And now you have an idea if someone has made a change. This is a far more accurate method than hoping to get an honest answer from someone in response to this simple question: “What changed?”
Besides troubleshooting you may want to keep track of having jobs created. This is especially useful in shops that purchase an enterprise-class job scheduler and as a result discourage the use of SQL Agent. In that case you may want to use similar code:
SELECT * FROM msdb..sysjobs WHERE DATEDIFF(hh, date_created, GETDATE()) < 24
You could run this code periodically throughout the day in order to determine if any jobs have been created in the past 24 hours. That would give you the opportunity to remind the creator of the job that they should move their job out of SQL Agent.
One of my favorite columns inside of the sysjobs table is the notify_level_eventlog column. This column indicates when a job should log a notification to the Application event log inside of Windows. You should see the following values:
0 = Never 1 = When the job succeeds 2 = When the job fails 3 = Whenever the job completes (regardless of the job outcome)
Why is this full of goodness? Well, for those of us using tools like Operations Manager it is important to know that your jobs are writing their notifications to the Application event log. If they don’t then Operations Manager will not raise a notification for you. Trust me, this is a good thing to check on from time to time, especially if you are using tools that monitor your event logs. By the way, if you create a new job inside of SQL Agent using SSMS you should note that by default that job you create will have this value set to zero. Warrants mentioning.
Where else is this information?
The msdb database contains all the detailed information for jobs within SQL Agent.
