Sql-server – SQL Server Management Studio Agent Jobs List View

There are several different ways to monitor the output of sql server jobs.

Option 1: Monitoring Tool, Such as Sitesope, MOM/SCOM, or Custom For most production instances, you want to have an enterprise monitoring tool which will scan for OS and SQL related errors. Typically you set your Sql Agent jobs to write to the Windows event log when they fail, and your monitoring tool frequently reads the Windows event log and will alert you based on conditions you define. You can purchase a monitoring system such as SiteScope, or create your own tool to look for these errors. You could also use a tool like Logparser to read these logs.

I mention this option first because if your reporting system is critical, you probably want to invest in a reliable monitoring system long term. Short term you could do some custom scripting if you wanted to go the Windows Log route.

Option 2: Querying MSDB All sql job history is stored in the MSDB database, and you can indeed query it. You can do this both from query windows or from a custom tool-- for instance, you could create a powershell script that periodically connects to your each of your servers and queries the msdb database for given conditions and alerts accordingly.

I have written a couple of blog entries which have sample scripts for querying sql job history. None of them do exactly what you're asking for, but they will help you work with the way dates are stored in MSDB, which can be a bit tricky as they aren't stored in datetime fields: http://thedbaknows.wordpress.com/category/sql-agent/

Hope this helps!

This query will give you all of the instances of your proc in your jobs and which database the step that calls the proc is running against, with one caveat: it won't tell you if there is a job that is executing an SSIS package which calls your proc.

SELECT 
    jobs.NAME AS JobName,
    steps.step_id AS StepID,
    steps.step_name AS StepName,
    steps.database_name AS DatabaseForJobStep
FROM msdb..sysjobsteps steps
JOIN msdb..sysjobs jobs ON steps.job_id=jobs.job_id
WHERE steps.command LIKE '%p_mystoredprocname%'
ORDER BY jobs.NAME,Steps.step_id