Query to see Jobs that run in SQL Server

Posted: November 26, 2012 in SQLServer
Tags: ,

1)

select *
from (
select
name,(CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4) run_date,run_duration
,(case run_status when 1 then ‘Success’
when 0 then ‘Failed’
when 2 then ‘Retry’
when 3 then ‘Canceled’
end) run_status
,message
from msdb..sysjobhistory h
inner join msdb..sysjobs j on h.job_id = j.job_id
where step_id=0) t
where
run_date >= CONVERT(VARCHAR(10),GETDATE()-1,111)
order by run_date desc

——————

2) sp_who3

3)

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
cast(req.total_elapsed_time / (1000 * 60) as varchar(10)) + ‘ minutes’,
cast(req.total_elapsed_time / (1000 * 60 * 60) as varchar(10)) + ‘+ hours’
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

4)

select distinct
job_name,
run_datetime_original,
run_datetime,
SUBSTRING(run_duration, 1, 2) + ‘:’ + SUBSTRING(run_duration, 3, 2) + ‘:’ + SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
select
job_name,
run_datetime run_datetime_original,
DATEADD(hh, -7, run_datetime) as run_datetime,
run_duration = RIGHT(‘000000’ + CONVERT(varchar(6), run_duration), 6)
from
(
select
j.name as job_name,
run_datetime = max(CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4)
from msdb..sysjobhistory h
inner join msdb..sysjobs j on h.job_id = j.job_id
group by j.name
) t
inner join msdb..sysjobs j on t.job_name = j.name
inner join msdb..sysjobhistory h on j.job_id = h.job_id and t.run_datetime = CONVERT(DATETIME, RTRIM(h.run_date)) + (h.run_time * 9 + h.run_time % 10000 * 6 + h.run_time % 100 * 10) / 216e4
) dt
order by run_datetime desc

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s