DBA Repository – Gather Scheduled Job Info

In previous posts I demonstrated how I gather server information and database information.  This article will use the same SSIS package to gather up scheduled job information from all the SQL Servers in the enterprise.  The steps to gather this data are relatively simple, loop through all the SQL Servers in the enterprise, query data from sysjobs and sysjobhistory in msdb, then store that data in the Repository for later analysis.

Create the JobHistory Table

My primary concern is which jobs succeeded or failed so I’m not gathering duration information, but that data is available in sysjobhistory so it can be added if desired.  Here’s the table I created to hold the job history:

CREATE TABLE dbo.JobHistory (
      JobHistoryID int IDENTITY(1, 1)
                       NOT NULL
    , JobServerName sysname NOT NULL
    , JobID uniqueidentifier NOT NULL
    , JobName sysname NOT NULL
    , JobOwner sysname NOT NULL
    , JobRunDate datetime NULL
    , JobStepID nvarchar(48) NULL
    , JobStepName sysname NOT NULL
    , JobStepSubsystem nvarchar(40) NOT NULL
    , JobStepResult int NOT NULL
    , JobStepResultDescription varchar(11) NULL
    , JobStepResultMessage nvarchar(4000) NULL
    , Collected datetime NOT NULL
    , CONSTRAINT i01JobHistory PRIMARY KEY NONCLUSTERED (JobHistoryID ASC)
    )
ALTER TABLE dbo.JobHistory
ADD CONSTRAINT DF_JobHistory_Created DEFAULT (GETDATE()) FOR Collected

New SSIS Tasks

In DBA Repository – Create the Server List I discussed how to loop through all the servers in the enterprise to connect and gather information.  I’m simply extending that process by adding the Gather Job History task in the screen shot below:

Gather Server and Database Configurations - 2

Get Last Job Info Collected Date Task

The Get Last Job Info Collected Date task runs a parameterized query to determine the last time we had a successful transfer of job history information so we only pull the most recent information

SELECT  LastCollected = ISNULL(MAX(Collected), '19800101')
FROM    dbo.JobHistory AS jh
WHERE   jh.JobServerName = ?

The parameter passed (?) is the current server name in the User::SQLServer variable that the Server Information Tasks loop loads for each iteration of the loop.  The result is stored in the User::LastCollected variable for use in the next task.

Transfer Job History Data Flow

The Transfer Job History data flow is uses an OLE DB Source with a SQL Command from user variable, User::TSQLSourceServerJobHistory.  I build the query as an expression in the variable so I can use the correct Last Collected data gathered from the previous step.  I must build the query as an expression in the variable because the query is too complicated for the OLE DB Source task to parse as a parameterized query.  Here’s the expression I use in the User::TSQLSourceServerJobHistory variable:

DECLARE @CollectDate datetime
DECLARE @StartDate datetime
DECLARE @ServerVersion varchar(20)
 
SET @CollectDate = GETDATE()
SET @ServerVersion = CAST(SERVERPROPERTY('ProductVersion') AS varchar(20))
SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.', @ServerVersion) - 1)
SET @StartDate = '" + (DT_WSTR, 25)@[User::JobDataLastCollected]  + "'
 
-- SQL 2000 and earlier
IF CAST(@ServerVersion AS tinyint) <= 8
    SELECT  JobServerName = jh.[server]
          , JobID = j.job_id
          , JobName = j.name
          , JobOwner = l.loginname
          , JobRunDate = CAST(STR(jh.run_date, 8, 0) + ' ' + LTRIM(STUFF(STUFF(RIGHT(REPLICATE('0', 6)
                                                                                     + LTRIM(STR(jh.run_time, 6, 0)), 6), 3, 0, ':'), 6, 0, ':')) AS datetime)
          , JobStepID = CAST(js.step_id AS nvarchar(48))
          , JobStepName = js.step_name
          , JobStepSubsystem = js.subsystem
          , JobStepResult = jh.run_status
          , JobStepResultDescription = CASE jh.run_status
                                         WHEN 0 THEN 'Failed'
                                         WHEN 1 THEN 'Succeeded'
                                         WHEN 2 THEN 'Retry'
                                         WHEN 3 THEN 'Canceled'
                                         WHEN 4 THEN 'In Progress'
                                         ELSE NULL
                                       END
          , JobStepResultMessage = CAST(jh.[message] AS nvarchar(4000))
          , Collected = @CollectDate
    FROM    msdb.dbo.sysjobs AS j
            INNER JOIN [master].dbo.syslogins AS l ON j.owner_sid = l.[sid]
            INNER JOIN msdb.dbo.sysjobhistory AS jh ON j.job_id = jh.job_id
            INNER JOIN msdb.dbo.sysjobsteps AS js ON jh.step_id = js.step_id
                                                     AND jh.job_id = js.job_id
    WHERE   1 = 1
            AND jh.step_id <> 0
            AND CAST(STR(jh.run_date, 8, 0) AS datetime) >= @StartDate
-- SQL 2005 and later
IF CAST(@ServerVersion AS tinyint) > 8
    SELECT  JobServerName = jh.[server]
          , JobID = j.job_id
          , JobName = j.name
          , JobOwner = l.name
          , JobRunDate = CAST(STR(jh.run_date, 8, 0) + ' ' + LTRIM(STUFF(STUFF(RIGHT(REPLICATE('0', 6)
                                                                                     + LTRIM(STR(jh.run_time, 6, 0)), 6), 3, 0, ':'), 6, 0, ':')) AS datetime)
          , JobStepID = CAST(js.step_uid as nvarchar(48))
          , JobStepName = js.step_name
          , JobStepSubsystem = js.subsystem
          , JobStepResult = jh.run_status
          , JobStepResultDescription = CASE jh.run_status
                                         WHEN 0 THEN 'Failed'
                                         WHEN 1 THEN 'Succeeded'
                                         WHEN 2 THEN 'Retry'
                                         WHEN 3 THEN 'Canceled'
                                         WHEN 4 THEN 'In Progress'
                                         ELSE NULL
                                       END
          , JobStepResultMessage = CAST(jh.[message] AS nvarchar(4000))
          , Collected = @CollectDate
    FROM    msdb.dbo.sysjobs AS j
            INNER JOIN msdb.sys.server_principals AS l ON j.owner_sid = l.[sid]
            INNER JOIN msdb.dbo.sysjobhistory AS jh ON j.job_id = jh.job_id
            INNER JOIN msdb.dbo.sysjobsteps AS js ON jh.step_id = js.step_id
                                                     AND jh.job_id = js.job_id
    WHERE   1 = 1
            AND jh.step_id <> 0
            AND CAST(STR(jh.run_date, 8, 0) AS datetime) >= @StartDate

This query will pull the data needed for SQL 2000 and later instances.   Additionally, it will only pull job history for jobs executed since the last time this data was gathered.  The data is transferred to an OLE DB Destination configured for fast load to the JobHistory table we created earlier.

Summary

Leveraging the work created earlier to query data from every SQL Server in the enterprise, I added a set of tasks to gather scheduled job information from all SQL Servers and store the data in the Repository.  This was accomplished by:

  • Determining the last successful data transfer for each server in the loop
  • Building a query to load only the job information since the last successful transfer
  • Dropping the results in the DBA Repository

Enjoy!

10 Responses to DBA Repository – Gather Scheduled Job Info

  1. DoodlingDBA says:

    hey DBADave,

    firstly thanks so much for posting your server check solution – it is such a great help, you’re a true dba-bodhisattva 🙂

    i’m having a problem with the ssis package for job collection info and it i think it has to do with the datetime variable in SSIS. Using your package, it works fine on the first run, but on subsequent runs it fails, with “The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.” If i put a pre-execute breakpoint on the User:JobDataLastCollected variable, i can see that the format is DD/MM/YYYY HH:MM:SS… the problem is the source servers may not be in that format as i have a combination of UK and US servers. So what i have tried to do is force the variable into a YYYY-MM-DD format by doing a conversion of User::JobDataLastCollected to 112 format, but when i watch the variable in the breakpoint, it is still defaulting back to DD/MM/YYYY…. it’s so frustrating! Do you have any tips on how i could resolve this?

    Thanks!

    Doodles

  2. DBA Dave says:

    I’ve not had to consider datetime regional setting issues where I use the DBARepoistory … oops 🙂

    You can fix this by converting the dates using 126 format. In the “Get Last Job Info Collected Date” task modify the query to this:

    SELECT  LastCollected = CAST(ISNULL(CONVERT(varchar(25), MAX(Collected), 126), CONVERT(varchar(25), '19800101', 126)) AS datetime)
    FROM    dbo.JobHistory AS jh
    WHERE   jh.JobServerName = ?

    This will ensure the date found at any server will come back in a universal format…

  3. DoodlingDBA says:

    Thanks for getting back to me. I had tried the solution above before it didn’t work. Despite converting the value, if you watched the variable in SSIS using breakpoints the date would still be in the dd/mm/yyyy format. In the end I had to configure the default language of the login configured to the various data sources to be British English instead of the default English (US) and it then worked as intended.

    Thanks!

    Doodles

  4. n says:

    Same problem here with multiple different SQL Servers with different collation settings.

    Where on the login do you set it to British English?

    The above convert using 126 does not work. Think this needs addressing

  5. N says:

    This doesn’t work correctly. Since suing this I have discovered that not all jobs are returned in the associated report, despite the table havibng records. The query
    SELECT jh.JobServerName
    , jh.JobName
    , LastRunDate = ljr.LastRunDate
    , LastRunStatus = ljs.JobStepResult
    FROM [server].JobHistory AS jh
    INNER JOIN (SELECT JobID
    , LastRunDate = MAX(JobRunDate)
    FROM [server].JobHistory
    GROUP BY JobID) AS ljr ON jh.JobID = ljr.JobID
    INNER JOIN (SELECT JobId
    , JobRunDate
    , JobStepResult
    FROM [server].JobHistory) AS ljs ON ljr.JobID = ljs.JobID
    AND ljr.LastRunDate = ljs.JobRunDate
    WHERE CONVERT(VARCHAR(25),jh.JobRunDate, 112) BETWEEN @JobStartDate AND @JobEndDate
    GROUP BY jh.JobServerName
    , jh.JobName
    , ljr.LastRunDate
    , ljs.JobStepResult
    ORDER BY ljr.LastRunDate DESC

    does not return all records for each server job. This may be due to the SSIS package or something else.

  6. N says:

    Still having problems with this particular task and the associated report.

    I am finding that not all jobs are necessarily being picked up in the Transfer Job History step and populated in the destination table – JobHistory.

    For example (copied from the JobHistory report): 7 jobs (this server has more like 20 jobs, each run atleats once a week)

    DatabaseBackup – USER_DATABASES – LOG 30/5/2013 6:00 Success

    syspolicy_purge_history 30/5/2013 2:00 Success

    sp_delete_backuphistory 1/5/2013 3:00 Success

    sp_purge_jobhistory 1/5/2013 3:00 Success

    syspolicy_check_schedule_7B21CD74-5F15-4B2D-BF2D-12BF2EF0E6FB 1/5/2013 3:00 Success

    CommandLog Cleanup 1/5/2013 3:00 Success

    Output File Cleanup 1/5/2013 3:00 Success

    I know for a fact that a differential job is run each evening @20:00, which is recorded in the sys tables, quick sample below:

    817B804A-770F-4049-82E5-AA4FA5ED5329, DatabaseBackup – USER_DATABASES – DIFF, 20130529

    Is not being recorded or picked up either in the SSIS package or query variable in Source Server Job History or combination of steps andor associated report.

    Any thoughts on this, since it is important it is picking up when a job was run?

  7. Thanks for letting me know about this N, I found the source of the problem. If the job has an owner but the owner doesn’t have a login on the server the INNER JOIN on dbo.sysjobs and sys.server_principals does not return the job. Converting that to an outer (LEFT) join and wrapping the sys.server_principals.name in an ISNULL solves problem. To apply the fix, open the DBARepository_GatherServerAndDatabaseConfigurations package > open the Transfer Job History data flow > view the properties of the TSQLSourceServerJobHistory variable > modify the expression property with the following expression:

    DECLARE @CollectDate datetime
    DECLARE @StartDate datetime
    DECLARE @ServerVersion varchar(20)
     
    SET @CollectDate = GETDATE()
    SET @ServerVersion = CAST(SERVERPROPERTY('ProductVersion') AS varchar(20))
    SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.', @ServerVersion) - 1)
    SET @StartDate = '" + (DT_WSTR, 25)@[User::JobDataLastCollected]  + "'
     
     
     
    -- SQL 2000 and earlier
    IF CAST(@ServerVersion AS tinyint) < = 8 
        SELECT  JobServerName = jh.[server]
              , JobID = j.job_id
              , JobName = j.name
              , JobOwner = ISNULL(l.loginname, '')
              , JobRunDate = CAST(STR(jh.run_date, 8, 0) + ' ' + LTRIM(STUFF(STUFF(RIGHT(REPLICATE('0', 6) + LTRIM(STR(jh.run_time, 6, 0)), 6), 3, 0, ':'), 6, 0, ':')) AS datetime)
              , JobStepID = CAST(js.step_id AS nvarchar(48))
              , JobStepName = js.step_name
              , JobStepSubsystem = js.subsystem
              , JobStepResult = jh.run_status
              , JobStepResultDescription = CASE jh.run_status
                                             WHEN 0 THEN 'Failed'
                                             WHEN 1 THEN 'Succeeded'
                                             WHEN 2 THEN 'Retry'
                                             WHEN 3 THEN 'Canceled'
                                             WHEN 4 THEN 'In Progress'
                                             ELSE NULL
                                           END
              , JobStepResultMessage = CAST(jh.[message] AS nvarchar(4000))
              , Collected = @CollectDate
        FROM    msdb.dbo.sysjobs AS j
                LEFT JOIN [master].dbo.syslogins AS l ON j.owner_sid = l.[sid]
                INNER JOIN msdb.dbo.sysjobhistory AS jh ON j.job_id = jh.job_id
                INNER JOIN msdb.dbo.sysjobsteps AS js ON jh.step_id = js.step_id
                                                         AND jh.job_id = js.job_id
        WHERE   1 = 1
                AND jh.step_id <> 0
                AND CAST(STR(jh.run_date, 8, 0) AS datetime) >= @StartDate
     
     
    -- SQL 2005 and later        
    IF CAST(@ServerVersion AS tinyint) > 8 
        SELECT  JobServerName = jh.[server]
              , JobID = j.job_id
              , JobName = j.name
              , JobOwner = ISNULL(l.name, '')
              , JobRunDate = CAST(STR(jh.run_date, 8, 0) + ' ' + LTRIM(STUFF(STUFF(RIGHT(REPLICATE('0', 6) + LTRIM(STR(jh.run_time, 6, 0)), 6), 3, 0, ':'), 6, 0, ':')) AS datetime)
              , JobStepID = CAST(js.step_uid as nvarchar(48))
              , JobStepName = js.step_name
              , JobStepSubsystem = js.subsystem
              , JobStepResult = jh.run_status
              , JobStepResultDescription = CASE jh.run_status
                                             WHEN 0 THEN 'Failed'
                                             WHEN 1 THEN 'Succeeded'
                                             WHEN 2 THEN 'Retry'
                                             WHEN 3 THEN 'Canceled'
                                             WHEN 4 THEN 'In Progress'
                                             ELSE NULL
                                           END
              , JobStepResultMessage = CAST(jh.[message] AS nvarchar(4000))
              , Collected = @CollectDate
        FROM    msdb.dbo.sysjobs AS j
                LEFT JOIN msdb.sys.server_principals AS l ON j.owner_sid = l.[sid]
                INNER JOIN msdb.dbo.sysjobhistory AS jh ON j.job_id = jh.job_id
                INNER JOIN msdb.dbo.sysjobsteps AS js ON jh.step_id = js.step_id
                                                         AND jh.job_id = js.job_id
        WHERE   1 = 1
                AND jh.step_id <> 0
                AND CAST(STR(jh.run_date, 8, 0) AS datetime) >= @StartDate
  8. N says:

    Great stuff Dave, I have tested on one server and it certainly returning more records. I’ll promote and check next week and let you know the outcome. Many thanks

  9. N says:

    Hi Dave, I can confirm it is returning the correct records. Quick ?, what made you narrow down the problem to owners\logins? I was looking at dates…

  10. First thing I always check when I’m not getting the records back I expect is the INNER JOIN on a query. Using INNER means a match on the joining column(s) must be in both sides of the join otherwise rows are not returned. I just comment out each join and start adding them back in one at a time until I find the one that drops off records unexpectedly and troubleshoot that one.

Leave a Reply

Your email address will not be published. Required fields are marked *