How to Search SQL Server Agent Jobs for a Specific Keyword?

Similar to being dropped off in the middle of a forest that you had never visited, you had been assigned to troubleshoot SQL Server Agent job failures on a new SQL Server environment that you had never worked on. You had asked for documentations and the server contacts. You have gotten laugther replies – the answer is no and the contacts are no longer with the employer. That shouldn’t be a surprise of the century for you. The requester acknowledges that while there are a high number of job failures throughout the network, the top priority is to find the job that execute the stored procedure that runs the “Flash Report” and it’s conveniently named “usp_RunDailyFlashReport” as the requester had learned from the former SQL Server DBAs. How can you find all the SQL Server Agent jobs that may run this stored proc in a New York minute or less? Let’s try the following script:

-- search the jobs for a specific text 
SELECT SERVERPROPERTY('SERVERNAME') as [InstanceName],
	j.job_id,
	j.name,
	js.step_id,
	js.command,
	j.enabled 
FROM	msdb.dbo.sysjobs j
JOIN	msdb.dbo.sysjobsteps js
	ON	js.job_id = j.job_id 
WHERE	js.command LIKE '%KEYWORD%' -- replace keyword with the word or stored proc that you are searching for
GO
Be Sociable, Share!
You can leave a response, or trackback from your own site.

Leave a Reply

You must be logged in to post a comment.