March 17, 2009 at 4:39 am
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?
March 17, 2009 at 8:34 am
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[/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]
March 17, 2009 at 8:41 am
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:
March 18, 2009 at 8:26 am
Brilliant guys!!!
Let me try!!
Il be back 😉
March 18, 2009 at 8:50 am
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
March 18, 2009 at 8:55 am
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?
March 18, 2009 at 8:57 am
I want to import on a scheduled, and it should ONLY import:
"WHERE trade_date >= '2009-03-18'
March 18, 2009 at 9:24 am
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)
February 15, 2010 at 12:05 am
what if there are conflicting JOB IDs when we insert from each instance into the centraljobHistory table?
February 15, 2010 at 4:27 am
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;-)
February 16, 2010 at 2:18 am
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