Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Data mining SQL Server Agent

, 24 Nov 2010
Rate this:
Please Sign up or sign in to vote.
Search job commands for text, find jobs by schedule and other spiffy things
In my particular situation, one of our main databases is being overhauled, resulting in the task of moving certain stored procedures to other schemas or databases. To do this we had to search the jobs in Agent to see which ones needed to be rewritten. The query turned out to be simple:
SELECT jobs.Name
FROM msdb.dbo.sysjobs jobs LEFT OUTER JOIN msdb.dbo.sysjobsteps steps
    ON jobs.job_id=steps.job_id
WHERE steps.command LIKE '%search text%'
So let's suppose you needed to move a stored procedure named sp_DoSomething. Use the code above with the procedure name in the LIKE clause will give you the jobs (if any) that execute sp_DoSomething.
 
The table msdb.dbo.sysjobs holds data about Agent jobs. Each step in the job is kept in msdb.dbo.sysjobsteps. You can get a listing of all the steps in a job with
SELECT steps.step_id, steps.step_name, steps.command
FROM msdb.dbo.sysjobs jobs LEFT OUTER JOIN msdb.dbo.sysjobsteps steps
    ON jobs.job_id=steps.job_id
WHERE jobs.name='job name'
ORDER BY steps.step_id
Another useful table is sysschedules, which holds the scheduling information for Agent jobs. The help file for this table gives more details, but if you wanted to find out what jobs run on the weekend, the query would look like this:
SELECT name
FROM msdb.dbo.sysschedules
WHERE freq_type=4 -- these run every day
OR (freq_type=8 -- these run weekly
     AND (freq_interval=1 -- on Sunday
        OR freq_interval=7 -- on Saturday
        OR freq_interval=10)) -- on both weekend days
If you want to return only active jobs, add
enabled=1
There are two other tables that are worth a look. sysjobschedule gives the list of upcoming jobs, including the next scheduled date and time, and sysjobhistory is your reference to past job execution with an entry for each job step giving date, time, how long the step took to run, the result of execution and other useful info.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Gregory Gadow
Software Developer (Senior)
United States United States
Gregory Gadow lives in Seattle, Washington and has been writing code for almost 25 years in more than a dozen programming languages. He works for a mid-size brokerage firm and holds the Series 7 and Series 66 brokerage licenses, but much prefers working as the company's programming department doing VB6, VB.Net, ASP, HTML, XML and SQL.

Comments and Discussions

 
GeneralReason for my vote of 3 average Pinmemberjoshijigar29-Nov-10 6:28 
GeneralReason for my vote of 5 Thank you for sharing. Awesome! PinmemberSlacker00724-Nov-10 0:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 24 Nov 2010
Article Copyright 2010 by Gregory Gadow
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid