Using SQL Server Management Studio (SSMS), we have the capability of quickly looking at job related information to verify things such as whether or not it is enabled, the last execution or the date and time it is scheduled to run next. As you can see by the below instance (figure 1), I don’t have many jobs. However, if you’ve ever been in an actual production environment, you would notice quite a bit more. What I want to quickly show our newer people to SSMS, is how filter through the hundreds or possibly thousands of jobs for the “one” you are looking for.
Figure 1: Job Activity Monitor
So fire up SSMS, connect to your Database Engine and navigate to the Job Activity Monitor under the SQL Server Agent within the Object Explorer. Double click the Job Activity Monitor and you should see something similar to figure 1 above.
To filter the results, just click the Filter button in the top of the screen. This should bring up modal screen like the one shown in figure 2 below. You can filter based on many things, such only the enabled jobs, the name of the job or even when it was last run. In this case, I have entered the Sales in the name filter. This will return any jobs with Sales in the name.
Figure 2: Job Activity Monitor - Filter Settings
And there you have it. I said that it was going to be a quick and easy one. I hope that this helps you find the job you are looking for.
Until next time, “keep your ear to the grindstone” – Good Will Hunting
Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting