MSDB Job History Tables

  • I have a number of instances on the same DB Server.

    I can see that each DB has job related tables within the the MSDB DB.

    I would like to create ONE central Table that stores all the DB job information.

    i.e.

    Table = CentralJobData

    This should import yesterdays data from MSDB tables:

    sysjobservers

    sysjobs

    For all the instances on the local server

    Can this be done?

    Any ideas how?

  • You can use linked servers to poll information from all the servers.

    Create you new CentralJobHistory table on server of your choice. (S1)

    Create a new login on each of the instances that has read permissions to the history table. (S2..Sn)

    Create linked server between S1 and S2..Sn.

    Create a job that imports data from S2.msdn.dbo.sysjobhistory to S1.db.dbo.CentralJobHistory.

    ... Thanks.

    Mohit.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • One option would be to create linked servers and then to create a view where you could do a union all to link tables together.

    Create a linked server:

    sp_addlinkedserver

    @server='Target1',

    @srvproduct='',

    @provider='SQLNCLI',

    @datasrc='SERVER'

    Create a view to use the linked server:

    CREATE VIEW vw_AllJobHistory

    AS

    select * from target1.msdb.dbo.sysjobs sj

    inner join target1.msdb.dbo.sysjobhistory sjh on sj.job_id=sjh.job_id

    union all

    select * from target2.msdb.dbo.sysjobs sj

    inner join target2.msdb.dbo.sysjobhistory sjh on sj.job_id=sjh.job_id

    GO

    .... Looks like i have been beaten to it ....:blush:



    Nuke the site from orbit, its the only way to be sure... :w00t:

  • Brilliant guys!!!

    Let me try!!

    Il be back 😉

  • DONE - Create you new CentralJobHistory table on server of your choice. (S1)

    DONE - Create a new login on each of the instances that has read permissions to the history table. (S2..Sn)

    DONE - Create linked server between S1 and S2..Sn.

    HELPCreate a job that imports data from S2.msdn.dbo.sysjobhistory to S1.db.dbo.CentralJobHistory.

    Do I use type: T_SQL Script?

    How whould I do the import?

    Thanks

  • Do you want to import on a scheduled basis or do you just want to link and then create a view which shows everything dynamically in real time?



    Nuke the site from orbit, its the only way to be sure... :w00t:

  • I want to import on a scheduled, and it should ONLY import:

    "WHERE trade_date >= '2009-03-18'

  • Create the table structure

    select sj.name,sjh.* into [CentralHistoryTable] from msdb..sysjobs sj

    inner join msdb..sysjobhistory sjh on sj.job_id=sjh.job_id

    where 1=0

    For each server you want to pull data from (Though this will initially import all data and then only missing data)

    insert into [CentralHistoryTable]

    select sj.name,sjh.* from [linkname].msdb.dbo.sysjobs sj

    inner join [linkname].msdb.dbo.sysjobhistory sjh on sj.job_id=sjh.job_id

    full outer join [CentralHistoryTable] hist on hist.server=sjh.server and sjh.instance_id=hist.instance_id

    where hist.name is null

    Though you should put a clustered index on the table. Maybe something like this.

    create clustered index CLX_HistoryTable on [CentralHistoryTable](run_date,run_time)

    and maybe an index on the search fields

    create index IX_HistoryTable on [CentralHistoryTable](server,instance_id)



    Nuke the site from orbit, its the only way to be sure... :w00t:

  • what if there are conflicting JOB IDs when we insert from each instance into the centraljobHistory table?

  • If this happens, put a ID int identity column and set composite key on job_id and ID.

    then you can go for Clus. Index

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Alternatively, add a column with the instance name to your CentralJob table and view and optionally set composite key on InstanceName and job_id e.g. your view could look like:

    CREATE VIEW vw_AllJobHistory

    AS

    select 'target1' InstanceName, * from target1.msdb.dbo.sysjobs sj

    inner join target1.msdb.dbo.sysjobhistory sjh on sj.job_id=sjh.job_id

    union all

    select 'target2', * from target2.msdb.dbo.sysjobs sj

    inner join target2.msdb.dbo.sysjobhistory sjh on sj.job_id=sjh.job_id

    GO

    Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g. <your code here>. You can find the IFCode tags under the INSERT options when you are writing a post.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply