2

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?

3

You can use this query directly to get all the names of jobs that include specific text (for example "MyText"

SELECT j.job_id, j.[name], s.command
FROM sysjobsteps s
right join dbo.sysjobs j on s.job_id = j.job_id
WHERE command LIKE '%MyText%'

** Execute query on multiple SQL Servers can be done using SSMS by creating a local server group or a Central Management Server and one or more server groups

Open SQL Server Management Studio -> View -> click Registered Servers In the "registered Servers" windows you can create groups and add servers. To execute query on a group simply right-click a server group, point to Connect, and then click New Query

** Execute query on multiple Azure SQL Databases can be done using Azure Elastic Job (you can search a recording of my lecture about Elastic Pool and Elastic Job).

2

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

USE msdb
GO
SELECT sj.name FROM sysjobsteps js
join sysjobs sj on js.job_id = sj.job_id
WHERE js.command LIKE '%stored_procedure_name%'

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.