How to identify the job running through SQLServer Agent using SQL?

— List processes currently running through SQLServer Agent
select spid, program_name, blocked from master..sysprocesses where program_name like ‘SQLAgent%’

spid program_name blocked
—— ——————————————————————————— ——-
148 SQLAgent – TSQL JobStep (Job 0x6CDB1221B7584941B63E44C95E805E43 : Step 1) 0

— Using the Job from program_name in Step 1, find information on the job
select job_id, name, description from msdb..sysjobs where job_id = convert(uniqueidentifier, 0x6CDB1221B7584941B63E44C95E805E43)
job_id name description
———————————— ————————————- ————
2112DB6C-58B7-4149-B63E-44C95E805E43 purge transaction

Advertisements

How to see job owner, Email operator, Event Notification, Email Notification on jobs scheduled?

Using the SELECT statement below one can list notications that occur for jobs scheduled on SQLServer.

SELECT –Job name
sj.name As JobName,
–Job owner
SUSER_SNAME(sj.owner_sid) As owner,
–Job category e.g. “Database Maintenance”, “Report Server”, “[Uncategorized (Local)]”
c.name as Category,
–Operator to be e-mailed (may be NULL)
o.name as EmailOperator,
–notification in Event log (notify when job fails, succeeds, always, never)
CASE sj.notify_level_eventlog
WHEN 0 THEN 'Never'
WHEN 1 THEN 'When the job succeeds'
WHEN 2 THEN 'When the job fails'
WHEN 3 THEN 'When the job completes (regardless of the job outcome)'
ELSE ‘UNKNOWN’ END As EventLogNotification,
–e-mail notification (notify when job fails, succeeds, always, never)
CASE sj.notify_level_email
WHEN 0 THEN 'Never'
WHEN 1 THEN 'When the job succeeds'
WHEN 2 THEN 'When the job fails'
WHEN 3 THEN 'When the job completes (regardless of the job outcome)'
ELSE ‘UNKNOWN’ END AS EmailNotification
FROM msdb.dbo.sysjobs sj
–E-Mail Operator
LEFT OUTER JOIN msdb.dbo.sysoperators o ON sj.notify_email_operator_id = o.id
–Job categories
LEFT OUTER JOIN msdb.dbo.syscategories C ON sj.category_id = c.category_id
WHERE –ignore auto-created jobs (Reporting Services schedules) uncomment if you want ignore
— NOT (sj.name LIKE '_____________-____-____-____________') AND
–only enabled Jobs
sj.enabled = 1
ORDER BY o.name, sj.name;

Sample Output:
JobName,Owner,Category,EmailOperator,EventLogNotification,EmailNotification
Test,sa,[Uncategorized (Local)],NULL,When the job fails,Never

How to see history of jobs scheduled in SQL Server?

Information on sysjobhistory
Information on sysjobs

— shows job history with the most recent one to show the most recent one with it’s run status
select job_name, run_datetime, run_duration, run_status
from
(
select job_name, run_datetime,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration,
run_status
from
(
select
j.name as job_name,
run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT(‘000000’ + CONVERT(varchar(6), run_duration), 6),
run_status = CASE run_status WHEN 1 Then 'Success'
WHEN 0 Then 'Failure'
WHEN 2 Then 'Retry'
WHEN 3 Then 'Cancelled'
WHEN 4 Then 'In-Progress'
Else 'Unknown: ' + Convert(VARCHAR(2), run_status)
End
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
where step_id = 0 — show first step
) t
) t
order by run_datetime desc, job_name

Output:
Test 2010-01-28 20:15:43.000 00:00:00 Failure
Test 2010-01-28 19:37:33.000 00:00:00 Success

Code used from: http://weblogs.sqlteam.com/tarad/archive/2009/06/10/SQL-Server-Script-to-Display-Job-History.aspx