FIND LIST OF JOBS AND THEIR SCHEDULE WITH STEP WISE

SELECT SJ.NAME, SJS.STEP_ID,SJS.STEP_NAME,SJS.COMMAND,
SJ.DESCRIPTION,
(SELECT NAME FROM MASTER.DBO.SYSLOGINS WHERE SID IN (SJ.OWNER_SID)) JOB_OWNER,
SJ.DATE_CREATED,SJ.DATE_MODIFIED, SJS.DATABASE_NAME, case when d.freq_type = 8 then 'Weekly' when d.freq_type = 4 then 'Daily' else 'None' end Schedule,
d.Freq_Subday_Interval Interval_in_Minutes,
case when len(d.active_start_time) = 5 then convert(varchar,left(d.active_start_time,1)) + ':' + convert(varchar,left(right(d.active_start_time,4),2)) + ':' + convert(varchar,right (d.active_start_time,2))
when len(d.active_start_time) = 4 then '00' + ':'+convert(varchar,left(d.active_start_time,2)) + ':'+convert(varchar,right(d.active_start_time,2))
when len(d.active_start_time) = 3 then '00:0' + convert(varchar,left(d.active_start_time,1)) + ':'+convert(varchar,right(d.active_start_time,2))
when len(d.active_start_time) = 2 then '00:00:' + convert(varchar,left(d.active_start_time,2))
when len(d.active_start_time) = 1 then '00:00:0' + convert(varchar,left(d.active_start_time,1))
else
convert(varchar,left(d.active_start_time,2)) + ':' + convert(varchar,left(right(d.active_start_time,4),2)) + ':' + convert(varchar,right (d.active_start_time,2)) End [Scheduled_Time]
FROM SYSJOBS SJ INNER JOIN SYSJOBSTEPS SJS
ON SJ.JOB_ID = SJS.JOB_ID
LEFT OUTER JOIN SYSJOBSCHEDULES SJSC ON SJ.JOB_ID = SJSC.JOB_ID
LEFT OUTER JOIN SYSSCHEDULES d ON SJSC.SCHEDULE_ID = d.SCHEDULE_ID
ORDER BY NAME

Sql Monitoring - Job Failure information

A common requiremnet in DBA Environment to findout what are the jobs got failure in the servers. The jobs use to perform for various activities like Mainteance, Performance Related, Projects related, Backups/restore, making Disaster recovery techniques etc. If the job got failure and no solution has been provided for that, there could be some problem in regular activities.

Following query helps to findout what are the jobs got failure and by getting the information solution can be provided. If required this can be created as procedure in MSDB to simplify the task.

----------------------------------------------------------------------------------
Create Proc Pr_MonitorFailure as
Begin
select b.name Job,
case when len(a.run_time) = 5 then convert(varchar,left(a.run_time,1)) + ':' + convert(varchar,left(right(a.run_time,4),2)) + ':' + convert(varchar,right (a.run_time,2))
when len(a.run_time) = 4 then '00' + ':'+convert(varchar,left(a.run_time,2)) + ':'+convert(varchar,right(a.run_time,2))
when len(a.run_time) = 3 then '00:0' + convert(varchar,left(a.run_time,1)) + ':'+convert(varchar,right(a.run_time,2))
when len(a.run_time) = 2 then '00:00:' + convert(varchar,left(a.run_time,2))
when len(a.run_time) = 1 then '00:00:0' + convert(varchar,left(a.run_time,1))
else
convert(varchar,left(a.run_time,2)) + ':' + convert(varchar,left(right(a.run_time,4),2)) + ':' + convert(varchar,right (a.run_time,2)) End [Last Run Datetime],
--run_duration,
ISNULL(SUBSTRING(CONVERT(varchar(7),run_duration+1000000),2,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),run_duration+1000000),4,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),run_duration+1000000),6,2),'') AS [Last Run Duration], Message,
case when run_status = 0 then 'Fail' else 'Other Reason' end run_status from
Sysjobhistory a inner join Sysjobs b on a.job_id = b.job_id
where run_status <> 1 and message not like '%The Job was invoked%'
End
----------------------------------------------------------------------------------

Maintain all jobs with a default owner account in Sql server

This is common requirement in DBA environment, like all created jobs in the server should run with specified username. Normally in production environment user will not perform the job manually and if it is on schedule also it should run with some service account. When creating a job it would be created with the default user who creates the job and there may be chances also to forget changing the job owner.

This query will helps to perform all jobs should be under default owner account.

declare @username varchar(100)
set @username = 'sa'
update sysjobs set owner_sid =
(select sid from master.dbo.syslogins where name = @username)
where name in (job_a, job_b,...... job_n)


* you can change the parameters as per your requirement

FIND LIST OF JOBS AND ITS DETAILS IN SQL SERVER

-- JOBWISE INFORMATION
select distinct a.name JOBNAME, isnull(DESCRIPTION,'No description available') DESCRIPTION,c.Name Category, isnull (b.database_name,'None') DATABASE_NAME, isnull(f.name, 'None') Job_Owner,
case when d.name is null then 'No Schedule' else d.name end Schedule,
isnull (case d.freq_type
when '1 ' then 'Once'
when '4' then 'Daily'
when '8' then 'Weekly'
when '16' then 'Monthly'
when '32' then 'Monthly relative'
when '64' then 'When SQL Server Agent starts' end, 'None') as Frequency,
isnull (case d.freq_interval
when '1' then 'None'
when '2' then 'Monday'
when '4' then 'Tuesday'
when '8' then 'Wednesday'
when '16' then 'Thursday'
when '32' then 'Friday'
when '64' then 'Saturday'
end,'None') as DAY,
case when active_start_time < 120000 then
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000),4) + ' AM','None') else
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000-12),4) + ' PM','None') END
JOB_start_time,
isnull (convert (varchar,d.Date_Created), 'None') Created_Date from sysjobs a
Inner join sysjobsteps b on
a.job_id = b.job_id
left outer join syscategories c on a.category_id = c.category_id
left outer join master.dbo.syslogins f on a.Owner_sid = f.sid
left outer join sysjobschedules e on e.job_id = a.job_id
left outer join sysschedules d on e.schedule_id = d.schedule_id
order by a.name

--JOB STEP WISE INFORMATION
select distinct a.name JOBNAME, isnull(DESCRIPTION,'No description available') DESCRIPTION,c.Name Category, isnull (b.database_name,'None') DATABASE_NAME, isnull(f.name,'None') Job_Owner,
b.step_id STEP, b.step_name STEPNAME,b.subsystem TYPE,b.command,
case when d.name is null then 'No Schedule' else d.name end Schedule,
isnull (case d.freq_type
when '1 ' then 'Once'
when '4' then 'Daily'
when '8' then 'Weekly'
when '16' then 'Monthly'
when '32' then 'Monthly relative'
when '64' then 'When SQL Server Agent starts' end, 'None') as Frequency,
isnull (case d.freq_interval
when '1' then 'None'
when '2' then 'Monday'
when '4' then 'Tuesday'
when '8' then 'Wednesday'
when '16' then 'Thursday'
when '32' then 'Friday'
when '64' then 'Saturday'
end,'None') as DAY,
case when active_start_time < 120000 then
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000),4) + ' AM','None') else
isnull(left(convert(varchar,convert(decimal,D.Active_start_time)/10000-12),4) + ' PM','None') END
JOB_start_time,
isnull (convert (varchar,d.Date_Created), 'None') Created_Date from sysjobs a
left outer join sysjobsteps b on
a.job_id = b.job_id
left outer join syscategories c on a.category_id = c.category_id
left outer join master.dbo.syslogins f on a.Owner_sid = f.sid
left outer join sysjobschedules e on e.job_id = a.job_id
left outer join sysschedules d on e.schedule_id = d.schedule_id
order by a.name