How to find the schedule jobs in SQL Server
I came across a situation where i was not able to see the jobs scheduled in the sqlserver due to lack to privileges. SQL server agent was not visible, as i have only readonly permission on all the databases.
Still i can see the jobs scheduled in the Agent, by quering MSDB database which host all the details..
select sj.name,sj.description,suser_sname(sj.owner_sid) 'Job Owner',sj.enabled,sj.date_created,sj.date_modified,
sjs.next_run_date,sjs.next_run_time from msdb..sysjobs sj join
msdb..sysjobschedules sjs on sj.job_id=sjs.job_id
order by sj.name
..
Above query will return the job name, owner name, and some important information needed for the user..
If more details are needed then please run the below query and get the details. Below query will give more details like run duration of job, next schedule run, last run details .
SET NOCOUNT ON
SELECT Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,
j.name AS job_name,
CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,
CASE jh.run_status WHEN 0 THEN 'Error Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress' ELSE
'Status Unknown' END AS 'last_run_status',
ja.run_requested_date as last_run_date,
CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,
ja.next_scheduled_run_date,
CONVERT(VARCHAR(500),jh.message) AS step_description
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id) from msdb.dbo.sysjobactivity) ORDER BY job_name,job_status
Hope it gets useful..
I came across a situation where i was not able to see the jobs scheduled in the sqlserver due to lack to privileges. SQL server agent was not visible, as i have only readonly permission on all the databases.
Still i can see the jobs scheduled in the Agent, by quering MSDB database which host all the details..
select sj.name,sj.description,suser_sname(sj.owner_sid) 'Job Owner',sj.enabled,sj.date_created,sj.date_modified,
sjs.next_run_date,sjs.next_run_time from msdb..sysjobs sj join
msdb..sysjobschedules sjs on sj.job_id=sjs.job_id
order by sj.name
..
Above query will return the job name, owner name, and some important information needed for the user..
If more details are needed then please run the below query and get the details. Below query will give more details like run duration of job, next schedule run, last run details .
SET NOCOUNT ON
SELECT Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,
j.name AS job_name,
CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,
CASE jh.run_status WHEN 0 THEN 'Error Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress' ELSE
'Status Unknown' END AS 'last_run_status',
ja.run_requested_date as last_run_date,
CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,
ja.next_scheduled_run_date,
CONVERT(VARCHAR(500),jh.message) AS step_description
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id) from msdb.dbo.sysjobactivity) ORDER BY job_name,job_status
Hope it gets useful..
