— 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 ———————————— ————————————- … Continue reading How to identify the job running through SQLServer Agent using SQL?
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 … Continue reading How to see job owner, Email operator, Event Notification, Email Notification on jobs scheduled?
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 … Continue reading How to see history of jobs scheduled in SQL Server?