How can I determine the status of a job?

by

I would like to point out that none of the T-SQL on this page will work precisely because none of them join to the syssessions table to get only the current session and therefore could include false positives.

See this for reference: What does it mean to have jobs with a null stop date?

You can also validate this by analyzing the sp_help_jobactivity procedure in msdb.

I realize that this is an old message on SO, but I found this message only partially helpful because of the problem. 

SELECT
    job.name, 
    job.job_id, 
    job.originating_server, 
    activity.run_requested_date, 
    DATEDIFF( SECOND, activity.run_requested_date, GETDATE() ) as Elapsed
FROM 
    msdb.dbo.sysjobs_view job
JOIN
    msdb.dbo.sysjobactivity activity
ON 
    job.job_id = activity.job_id
JOIN
    msdb.dbo.syssessions sess
ON
    sess.session_id = activity.session_id
JOIN
(
    SELECT
        MAX( agent_start_date ) AS max_agent_start_date
    FROM
        msdb.dbo.syssessions
) sess_max
ON
    sess.agent_start_date = sess_max.max_agent_start_date
WHERE 
    run_requested_date IS NOT NULL AND stop_execution_date IS NULL

More Related Contents:

  1. Insert results of a stored procedure into a temporary table
  2. Eliminate and reduce overlapping date ranges
  3. Get top 1 row of each group
  4. Referring to a Column Alias in a WHERE Clause
  5. How to SELECT FROM stored procedure
  6. Convert Month Number to Month Name Function in SQL
  7. Function vs. Stored Procedure in SQL Server
  8. SET versus SELECT when assigning variables?
  9. Generate a resultset of incrementing dates in TSQL
  10. Passing an array of parameters to a stored procedure
  11. Getting result of dynamic SQL into a variable for sql-server
  12. How to get difference between two rows for a column field?
  13. Better techniques for trimming leading zeros in SQL Server?
  14. How to print VARCHAR(MAX) using Print Statement?
  15. What’s the best way to select the minimum value from several columns?
  16. How to assign an exec result to a sql variable?
  17. Concatenate values based on ID
  18. Delete duplicate records from a SQL table without a primary key
  19. SQL Server SELECT into existing table
  20. Splitting delimited values in a SQL column into multiple rows
  21. How to check if a stored procedure exists before creating it
  22. varbinary to string on SQL Server
  23. SQL : in clause in stored procedure:how to pass values
  24. SQL Server 2005 ROW_NUMBER() without ORDER BY
  25. Can an INNER JOIN offer better performance than EXISTS
  26. How can I generate a temporary table filled with dates in SQL Server 2000?
  27. Stored procedure that exports data into csv files only exports to one file
  28. What does collation mean?
  29. Memory effective way to read BLOB data in C#/SQL 2005
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment