You need some useful scripts for running SQL Server jobs, see status, etc.
SOLUTION:
-- Start job from script
EXEC msdb.dbo.sp_start_job N'DB_Backup';
-- List all jobs with basic info like job_id, name, etc.
SELECT * FROM msdb.dbo.sysjobs ORDER BY name
-- STOP a currently executing job
EXEC msdb.dbo.sp_stop_job N'DB_Backup' ;
-- Get job INFO on application job
EXEC msdb.dbo.sp_help_job
@job_name =
N'DB_Backup',
@job_aspect =
N'ALL' ;
-- Check run status of a job. Running = 1 means
currently executing
DECLARE @job_id uniqueidentifier = 'B6371A83-93EC-4176-AB95-0423B700E880'
EXEC master.dbo.xp_sqlagent_enum_jobs 1, sa, @job_id
-- If current_execution_status=1 then RUNNING
EXEC msdb.dbo.sp_help_job @job_id='0D823348-2DDC-4816-93EA-307D6E736437'
-- SQL Server Agent disable job
EXEC msdb.dbo.sp_update_job
@job_name =
N'DB_Backup',
@enabled =
0 ;
-- SQL Server basic job history information for rolling month
SELECT JobName,
RunTime,
Duration,
Status,
Server
FROM (SELECT JobName = job_name,
RunTime = run_datetime,
Duration = SUBSTRING(run_duration,1,2) + ':' +
SUBSTRING(run_duration,3,2) + ':' +
SUBSTRING(run_duration,5,2),
Status
= run_status,
Server
= [server]
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) / 2160000,
run_duration = RIGHT('000000' +
CONVERT(VARCHAR(6),run_duration), 6),
run_status = CASE run_status
WHEN 0 THEN 'Failure'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'Running'
ELSE 'Other: ' +
Convert(VARCHAR,run_status)
END,
server
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
WHERE step_id = 0) x) z
WHERE DATEDIFF(mm,RunTime,getdate()) < 1
ORDER BY RunTime DESC
-- SQL Server Agent detailed job information
SELECT job.name AS JobName,
job.job_id,
job.enabled AS JobEnabled,
CONVERT(SYSNAME,SERVERPROPERTY('Servername')) AS Server,
job.DESCRIPTION,
job.notify_level_eventlog,
job.notify_level_email,
job.notify_level_page,
job.notify_email_operator_id,
job.date_created,
cat.name AS CategoryName,
sch.next_run_date,
sch.next_run_time,
srv.last_run_outcome,
srv.last_outcome_message,
srv.last_run_date,
srv.last_run_time,
srv.last_run_duration,
op.name AS NotifyOperatorName,
op.email_address,
job.date_modified AS JobDateModified,
ssch.name AS ScheduleName,
ssch.enabled AS ScheduleEnabled,
ssch.freq_type,
ssch.freq_interval,
ssch.freq_subday_interval,
ssch.freq_subday_type,
ssch.freq_relative_interval,
ssch.freq_recurrence_factor,
ssch.active_start_date,
ssch.active_end_date,
ssch.active_start_time,
ssch.active_end_time,
GETDATE() AS ThisReportRunDate
FROM msdb.dbo.sysjobs job
INNER JOIN msdb.dbo.syscategories cat
ON job.category_id = cat.category_id
LEFT OUTER JOIN msdb.dbo.sysoperators op
ON job.notify_page_operator_id =
op.id
LEFT OUTER JOIN msdb.dbo.sysjobservers
srv
ON job.job_id = srv.job_id
LEFT OUTER JOIN msdb.dbo.sysjobschedules
sch
ON sch.job_id = job.job_id
LEFT OUTER JOIN msdb.dbo.sysschedules
ssch
ON sch.schedule_id = ssch.schedule_id
ORDER BY JobName
If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!
