How to find a SQL Job details in Production environment?

by Sri   Last Updated June 18, 2018 02:06 AM

There is a stored procedure being executed daily which inserts a record in a specific table. Our Production database servers has lot of SQL jobs scheduled to run daily at different intervals.

How to find out which job is running this particular stored procedure? Is there a way to find using SQL scripts?

Answers 1

You need to search the command column in the msdb sysjobsteps table of each server.

USE msdb
SELECT * FROM sysjobsteps WHERE command LIKE '%stored_procedure_name%'

Once you find the job step that is running it, you'll use the job_id column to find the associated job in the sysjobs table. See dbo.sysjobsteps.

I typically use a PowerShell script to iterate through all of my servers when I need to run something like this on all servers. That is a bit beyond the scope of this question, though.

Tony Hinkle
Tony Hinkle
June 18, 2018 01:55 AM

Related Questions

Is a rigorous schedule always view serializable?

Updated January 18, 2018 10:06 AM