Get Information on All Jobs running on a SQL server Instance.

The SQL server Jobs which are running on a Instance are very Important. As a DBA you need to take care of these Jobs and have all information about the Job. Your will get information on Jobs is SysJobs
but again not in the way you want. So just thought of pulling the Job information and below is the stored 
Procedure for the same.
This procedure give information on 

[job_id],

[Jobs],

[Category],

[Owner],

[Schedule Name],

[enabled],

[Frequecy],

[DateCreated],

[StartDate],

[NextRunDate],

[NextRunTime]

USE YourDatabaseName
GO
IF OBJECT_ID('dbo.spGetJobInfo') IS NOT NULL
DROP PROCEDURE dbo.spGetJobInfo
GO
CREATE PROCEDURE dbo.spGetJobInfo
AS
SET NOCOUNT ON
DECLARE @jobid VARCHAR(50)
DECLARE @sql NVARCHAR(1000)
CREATE TABLE #Jobs_Schedule(
            [schedule_id] [INT],
            [name] [SYSNAME],
            [enabled] [INT],
            [freq_type] [INT],
            [freq_interval] [INT],
            [freq_subday_type] [INT],
            [freq_subday_interval] [INT],
            [freq_relative_interval] [INT],
            [freq_recurrence_factor] [INT],
            [active_start_date] [INT],
            [active_end_date] [INT],
            [active_start_time] [INT],
            [active_end_time] [INT],
            [date_created] [DATETIME],
            [desc] VARCHAR(1000),
            [next_run_date] [INT],
            [next_run_time] [INT],
            [schedule_uid]    VARCHAR(1000),
            [job_count] [INT]
            ) ON [PRIMARY]
DECLARE c1 CURSOR FOR SELECT job_id FROM msdb..sysjobs
OPEN c1
    FETCH NEXT FROM c1 INTO @jobid
    WHILE @@fetch_status=0
        BEGIN
            INSERT #Jobs_Schedule EXEC ('msdb..sp_help_jobschedule @job_id='''+ @jobid+''',@include_description=1')
            FETCH NEXT FROM c1 INTO @jobid
        END
CLOSE c1
DEALLOCATE c1

CREATE TABLE #TempJoin
(
    job_id UNIQUEIDENTIFIER,
    Jobs VARCHAR(250),
    Category VARCHAR(250),
    OwnerName  VARCHAR(100),
    ScheduleName  VARCHAR(250),
    ISenabled  CHAR(4),
    Frequency VARCHAR(250),
    Date_Created  DATETIME,
    Active_Start_Date INT,
    Next_Run_Date  INT,
    Next_Run_Time INT
)
INSERT INTO #TempJoin
(
    job_id,
    Jobs,
    Category,
    OwnerName,
    ScheduleName,
    ISenabled,
    Frequency,
    Date_Created,
    Active_Start_Date,
    Next_Run_Date,
    Next_Run_Time
)
(
SELECT 
a.[job_id],
a.[Jobs],
a.[Category],
l.[name] AS [Owner],
a.[Schedule Name],
a.[enabled],
temp.[desc] AS [Frequecy],
temp.date_created AS [DateCreated],
temp.active_start_date AS [StartDate],
temp.next_run_date AS [NextRunDate],
temp.next_run_time AS [NextRunTime]
FROM #Jobs_Schedule AS temp 
RIGHT OUTER JOIN 
(SELECT
    j.job_id,
    j.[name] AS [Jobs], 
    j.owner_sid, 
    c.[name] AS [Category], 
    js.schedule_id, 
    s.[name] AS [Schedule Name], 
    CASE j.enabled WHEN 1 THEN 'YES' ELSE 'NO' END AS [enabled] 
    FROM msdb..syscategories AS c 
    RIGHT OUTER JOIN 
    msdb..sysjobs AS j ON c.category_id = j.category_id
    LEFT OUTER JOIN 
    msdb..sysjobschedules AS js ON j.job_id = js.job_id
    LEFT OUTER JOIN 
    msdb..sysschedules AS s ON js.schedule_id = s.schedule_id)
AS a ON temp.schedule_id = a.schedule_id 
LEFT OUTER JOIN 
sys.server_principals AS l ON a.owner_sid = l.sid)

CREATE TABLE #TempJoin1
(
    Name VARCHAR(250),
    job_id UNIQUEIDENTIFIER,
    LastRunStatus VARCHAR(25),
    LastRunDate INT,
    LastRunTIme INT,
)
INSERT INTO #TempJoin1
(
    Name,
    job_id,
    LastRunStatus,
    LastRunDate,
    LastRunTIme
)
(
SELECT  
   j.[name] AS [JobName],
   j.[job_id],  
   run_status = CASE h.run_status  
   WHEN 0 THEN 'Failed' 
   WHEN 1 THEN 'Succeeded' 
   WHEN 2 THEN 'Retry' 
   WHEN 3 THEN 'Canceled' 
   WHEN 4 THEN 'In progress' 
   END, 
   h.run_date AS LastRunDate,   
   h.run_time AS LastRunTime 
   FROM msdb..sysjobhistory h  
   INNER JOIN
   msdb..sysjobs j ON h.job_id = j.job_id  
   WHERE h.instance_id IN  
   (SELECT MAX(h.instance_id)  
        FROM msdb..sysjobhistory h GROUP BY (h.job_id))
)

SELECT 
    t1.Jobs,
    t1.OwnerName,
    t1.Category,
    t1.Date_Created,
    t1.Active_Start_Date,
    t1.ISenabled,
    t2.LastRunDate,
    t2.LastRunTIme,
    t2.LastRunStatus
    FROM #TempJoin t1
    INNER JOIN #TempJoin1 t2
    ON t1.job_id = t2.job_id

DROP Table #Jobs_Schedule
DROP Table #TempJoin
DROP Table #TempJoin1
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s