Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

MSDB Job History Tables Expand / Collapse
Author
Message
Posted Tuesday, March 17, 2009 4:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 9, 2013 9:57 AM
Points: 322, Visits: 718
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?

Post #677312
Posted Tuesday, March 17, 2009 8:34 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 31, 2014 2:57 PM
Points: 942, Visits: 1,063
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.


---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
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.


How to ask for help .. Read Best Practices here.
Post #677516
Posted Tuesday, March 17, 2009 8:41 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 4:01 AM
Points: 734, Visits: 645
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 ....




Nuke the site from orbit, its the only way to be sure...
Post #677524
Posted Wednesday, March 18, 2009 8:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 9, 2013 9:57 AM
Points: 322, Visits: 718
Brilliant guys!!!

Let me try!!
Il be back
Post #678532
Posted Wednesday, March 18, 2009 8:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 9, 2013 9:57 AM
Points: 322, Visits: 718
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
Post #678566
Posted Wednesday, March 18, 2009 8:55 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 4:01 AM
Points: 734, Visits: 645
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...
Post #678574
Posted Wednesday, March 18, 2009 8:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 9, 2013 9:57 AM
Points: 322, Visits: 718
I want to import on a scheduled, and it should ONLY import:
"WHERE trade_date >= '2009-03-18'

Post #678579
Posted Wednesday, March 18, 2009 9:24 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 4:01 AM
Points: 734, Visits: 645
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...
Post #678615
Posted Monday, February 15, 2010 12:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 15, 2010 4:32 AM
Points: 5, Visits: 27
what if there are conflicting JOB IDs when we insert from each instance into the centraljobHistory table?
Post #865379
Posted Monday, February 15, 2010 4:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #865453
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse