May 11, 2021 at 8:11 pm
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.
Kind regards.
May 12, 2021 at 9:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
May 14, 2021 at 11:22 pm
If you stop and think about it, all the supposed light-weight things you propose are just another way of capturing history except they are more heavy weight.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 17, 2021 at 3:35 pm
xevents is very lightweight, but since jobhistory is going to get this, not sure you querying text is better than a table.
May 17, 2021 at 4:32 pm
I am a bit confused by this request - as agent jobs are normally setup with a schedule and run on that schedule. How are you going to determine that these agent jobs are no longer needed if they are executed every day/month/quarter by the agent?
The only thing you can monitor for is someone executing sp_start_job - which would indicate that there are agent jobs that have no schedule. If the agent job doesn't have a schedule, then you already have a way of identifying those jobs - then it is just a matter of following up on those jobs to identify what group is responsible for that process. In fact - you can do that for scheduled jobs too but it will take some analysis, which you are going to have to do anyways. Regardless of how the job starts - you need to figure out what that job does before you can determine if it is needed.
Waiting for someone to login and execute sp_start_job doesn't seem like a good way to identify who is running these non-scheduled agent jobs. You should be able to identify all logins/users that have access to perform that function - it has to be granted after all. Once you have that list of accounts - follow up with those individuals and find out what they are running and when it is run.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 17, 2021 at 6:36 pm
Re monitoring scheduled jobs... For sure, by definition, we can be certain that scheduled jobs aren't going to make it to the deprecation list. I guess my thought was to capture the execution of every job for completeness....so that can I report on things such as any ad hoc executions that overlap with scheduled jobs, if someone implemented a process with any manual job dependencies, etc. But yes, ultimately, it's not critical.
After reading everyone's comments, it looks like polling job history on some frequency and then writing to a table might be my best option. That method makes it simple to include (or exclude) scheduled jobs via their enabled flag. I just wish I could do it via SQL Audit & XEvents, largely because of the additional metadata they so easily provide. That is to say, things such as client machine name, IP address, the calling Program/Application, etc., are readily available. Otherwise, I would have to query some system tables and DMVs to get that information. But I don't think all that is critical, either.
Anyway, I was surprised to learn that SQL doesn't call sp_start_job internally when running a scheduled job.
I very much appreciate everyone's suggestions & comments. Thank you.
May 19, 2021 at 11:04 am
keep min mind, a disabled job can still be fired using sp_start_job !
( it will just not be started by its coupled schedules or alerts )
Just in case you let your apps directly start sqlagent jobs.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply