SQL Server Agent Jobs, how do I query the status?

If you need to find the current status of the SQL Server Agent Jobs the following query is usefull

 

select name, description,start_execution_date,  stop_execution_date, 
msdb.dbo.sysjobs.Job_ID,
job_status = CASE
WHEN start_execution_date IS NULL AND stop_execution_date IS NULL THEN 0
WHEN start_execution_date IS NOT NULL AND stop_execution_date IS NULL THEN 1
WHEN start_execution_date IS NOT NULL AND stop_execution_date IS NOT NULL THEN 2
ELSE 3
END
FROM msdb.dbo.sysjobactivity JA inner join
msdb.dbo.sysjobs
ON JA.Job_ID = msdb.dbo.sysjobs.Job_ID
 



Regards,

Ing. Eduardo Castro Martínez, PhD – Microsoft SQL Server MVP

http://mswindowscr.org

http://comunidadwindows.org

Costa Rica

Technorati Tags: SQL Server

LiveJournal Tags: SQL Server

del.icio.us Tags: SQL Server

http://ecastrom.blogspot.com

http://ecastrom.wordpress.com

http://ecastrom.spaces.live.com

http://universosql.blogspot.com

http://todosobresql.blogspot.com

http://todosobresqlserver.wordpress.com

http://mswindowscr.org/blogs/sql/default.aspx

http://citicr.org/blogs/noticias/default.aspx

http://sqlserverpedia.blogspot.com/

Note: Cross posted from Eduardo Castro.

Permalink

Comments

Popular posts from this blog

Desempeño de SQL Server 2008 R2 y Max Worker Threads

Cómo identificar consultas más pesadas en SQL Server

SQL Server La longitud de los datos LOB (2200100) que se van a replicar excede el máximo configurado 65536.