How can I determine the status of a job?

Delving Deeper: Advanced Techniques for Job Status

Unwrapping the Activity and Session Information Package

Suspecting a currently active job? You'll need some extras! The sysjobactivity and syssession tables, when connected with the sysjobs_view, reveal more intriguing information:

SELECT sj.name, 
       sja.run_requested_date, 
       sja.stop_execution_date, 
       DATEDIFF(SECOND, sja.run_requested_date, GETDATE()) 
           AS 'Elapsed Seconds'
FROM msdb.dbo.sysjobs_view sj
JOIN msdb.dbo.sysjobactivity sja ON sj.job_id = sja.job_id
JOIN msdb.dbo.syssessions ss ON sja.session_id = ss.session_id
WHERE sja.run_requested_date IS NOT NULL 
      AND sja.stop_execution_date IS NULL 
      AND ss.agent_start_date <= sja.run_requested_date
ORDER BY Elapsed Seconds DESC;


This will fetch all active jobs and their running times, sorted beginning from the longest running.

Looking Back: Past-Execution Outcomes Review

For the history buffs out there, querying msdb.dbo.SysJobHistory provides an interesting peek back:

SELECT 
    sj.name, 
    sjh.run_date, 
    sjh.run_time, 
    sjh.message
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobhistory sjh ON sj.job_id = sjh.job_id
WHERE sjh.step_id = 0 AND sjh.run_status = 0 -- Time traveling to the land of Job Failures!
ORDER BY sjh.run_date DESC, sjh.run_time DESC;


This provides historical data on job failures, complete with time stamps and charming error messages!

Taking Pulse: Real-time job activity

Pop in and see what's cooking right now! The sp_help_job stored procedure serves up data hot off the job grill:

EXEC msdb.dbo.sp_help_job @execution_status = 1; -- Somebody call 911, because these jobs are on fire!


This delivers a snapshot of currently active jobs, just in the nick of SQL execution time.

Mind the Details: Deep Analysis and Prevention

Knowledge is Power: Understanding Job System Tables

Being familiar with MSDN documentation on job-related system tables is akin to knowing the minions who do behind-the-scenes work. These smart tables store metadata and logs about jobs, hosting deep analysis and troubleshooting:

• sysjobs: A roll call list for all jobs.
• sysjobactivity: A diary of current and past job activities.
• sysjobhistory: Personal notes of executed job outcomes.
• sysjobsteps: An itinerary of steps associated with each job.
• sysjobservers: The introverted tables giving a detail about the servers involved with each job.

False Positives: Sneak-thieves Caught!

When combining tables like sysjobactivity and syssessions, it's savvy to limit the joins to the current session. This helps you avoid putting older session data in the 'Currently in Action' cabinet:

WHERE ss.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)


Job Activity Peek Boxes

Use sp_help_jobactivity stored procedure to get a running commentary of current job hustle-bustle:

EXEC msdb.dbo.sp_help_jobactivity;


Taking Stock of Time

For those running jobs, the ticking alarm is elapsed time. Here's how you could play the timekeeper using DATEDIFF:

SELECT DATEDIFF(SECOND, run_requested_date, GETDATE()) FROM msdb.dbo.sysjobactivity;
