All job related system table in msdb system
database. msdb is a system database which will keep the information from SQL
Server Agent like scheduled, back-up and restore history information. The list
of system tables related to job’s are
1.
msdb.dbo.sysjobs
2.
msdb.dbo.sysjobsteps
3.
msdb.dbo.sysjobschedules
4.
msdb.dbo.sysjobservers
5.
msdb.dbo.sysjobhistory
6.
msdb.dbo.sysjobactivity (SQL
Server 2005 + versions)
7.
msdb.dbo.sysjobstepslog (SQL
Server 2005 + versions)
Find the list’s of jobs in the SQL Server.
SELECT * FROM
msdb.dbo.sysjobs;
Find all the enables jobs in the server
SELECT
JOB.job_id,name,enabled,description,server,database_name
FROM
msdb.dbo.sysjobs JOB
INNER
JOIN
msdb.dbo.sysjobsteps Jobsteps
ON
JOB.job_id =
JobSteps.job_id
WHERE JOB.enabled
= 1
Script to check whether the job is raised
any errors. Use the status field to get filter as per your need.
select
j.name, cast(convert(char(8),convert(int,jh.run_date)) as datetime) rundate,
LEFT(RIGHT('000000' + CAST(jh.run_time AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000'
+ CAST(jh.run_time AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(jh.run_time AS VARCHAR(10)),6),2) as RunTime
,jh.step_name,message,
CASE
jh.run_status
WHEN
0 THEN 'Failed'
WHEN
1 THEN 'Succeeded'
WHEN
2 THEN 'Retry'
WHEN
3 THEN 'Canceled'
ELSE
'Unknown'
END
as Status,jh.*
from
msdb.dbo.sysjobhistory jh
inner join msdb.dbo.sysjobs j on j.job_id = jh.job_id
To get the next running schedule time for
scheduled jobs are given below.
select
s.name Schedule,job.name Job,
cast(convert(char(8),convert(int,js.next_run_date)) as datetime) Rundate,
LEFT(RIGHT('000000' + CAST(js.next_run_time AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000'
+ CAST(js.next_run_time AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(js.next_run_time AS VARCHAR(10)),6),2) as RunTime
from
msdb.dbo.sysjobschedules
js
inner
join
msdb.dbo.sysschedules s on js.schedule_id = s.schedule_id
inner join msdb.dbo.sysjobs job on job.job_id = js.job_id
Steps to create jobs
1.
Execute sp_add_job to create a job
2.
Execute sp_add_jobstep to create one or more job steps
3.
Execute sp_add_schedule to create a schedule
4.
Execute sp_attach_schedule to attach a schedule to the job
5.
Execute sp_add_jobserver to set the server for the job
We have to call sp_add_jobserver at last,
because SQL Server Agent doesn’t cache the job until sp_add_jobserver is
called.