The idea is simple. We have a couple hundred jobs and we'd like to get rid of those no longer in use. The plan is to capture details on ad hoc job executions (e.g., login/user, client ip address, program/application, sql text, etc.) in order to determine their necessity. For example, if we have a job that's called only quarterly, we'll need to know. Plus, we may want to follow up with the user for details.
Of course, you could poll msdb job history data on some schedule, write to a table, query some DMVs to lookup some of the aforementioned info, etc. And maybe that's what I'll need to do. But a custom solution seems like overkill.
SQL Audit, XEvents, or a lightweight server-side trace seem like a perfect fit, especially since we can monitor continuously. I can easily filter on the execution of [sp_start_job] to get everything I need. Here's the problem...
For scheduled jobs, it doesn't look like SQL Server calls [sp_start_job] internally. And so I can't figure out how to grab those events. Taken a step further, are there other ways of running a job that my logic will fail to capture?
If I forgot something, I'll add it to the comments.