Retrive old JobHistory

I have a job which only keeps 100 entries in the job history. When one new
entry comes, the oldest entry in the list is deleted.
I need retrieve the old job histories that have been removed from the
current job history list. How can I do it? Restore DB is not an option.
Thanks a lot,
LixinYou will need to restore msdb(as different db) and look at the table
sysjobhistory, I recommend you iether increase the number of history rows
for that job or write a job which copies the rows to an archive table.
Yovan
"Lixin Fan" <nospam@.hotmail.com> wrote in message
news:uYx1XbouDHA.2712@.tk2msftngp13.phx.gbl...
> I have a job which only keeps 100 entries in the job history. When one new
> entry comes, the oldest entry in the list is deleted.
> I need retrieve the old job histories that have been removed from the
> current job history list. How can I do it? Restore DB is not an option.
> Thanks a lot,
> Lixin
>|||Hi Lixin
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
I think a trigger will be a workaround to you question.
Suppose you want to record the job history of Job_test, and the job_id is
={07C00C79-AAB0-49B0-BA0A-9E7C884440DB} ( you can get this information from
two tables in database 'msdb': 'sysjob' and 'sysjobhistory').
Suppose you have a table 'job_history' in database 'Database_test', and the
table 'job_history' has the same schema with the table 'sysjobhistory' in
msdb and you want to use this table to save the job history. You can add a
trigger to the sysjobhistory.
The thinking is: when the system add a new entry into the 'sysjobhistory',
check if this new entry is for job_test. If no, then take no action. If
yes, then the trigger will copy this entry in the job_history table in
database_test. By this way, you can record all the job history when the job
occurred.
The code for the trigger would be like this:
CREATE TRIGGER [jobtest] ON [dbo].[sysjobhistory]
FOR insert
AS
Declare @.job_date int
Declare @.job_time int
Declare @.job_idx varchar
Set @.job_idx=' ({07C00C79-AAB0-49B0-BA0A-9E7C884440DB}'
If @.job_idx<> (select top 1 job_id from [msdb].[dbo].[rundate] where
job_id=@.job_idx order by rundate,runtime asc)
--if the latest entry in the sysjobhistory is not caused by the job you
want to record
--no action
Begin
Return
End
--If the job you want to record add a new entry
Else
Begin
Insert [database_test].[dbo].[job_history].[rundate] value (select top 1 *
from [msdb].[dbo].[runtime] where job_id=@.job_idx order by rundate,runtime
asc
End
Note:
1) This code is just a thinking, not a tested one
2) This method will add the workload on your system, especially on a system
which has many concurrent jobs.
I hope this will help you to solve your problem. If you still have
questions, please feel free to post message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Great help. Thank you very much.
Lixin
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:NMNq6HxuDHA.2900@.cpmsftngxa07.phx.gbl...
> Hi Lixin
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
> your issue.
> I think a trigger will be a workaround to you question.
> Suppose you want to record the job history of Job_test, and the job_id is
> ={07C00C79-AAB0-49B0-BA0A-9E7C884440DB} ( you can get this information
from
> two tables in database 'msdb': 'sysjob' and 'sysjobhistory').
> Suppose you have a table 'job_history' in database 'Database_test', and
the
> table 'job_history' has the same schema with the table 'sysjobhistory' in
> msdb and you want to use this table to save the job history. You can add a
> trigger to the sysjobhistory.
> The thinking is: when the system add a new entry into the 'sysjobhistory',
> check if this new entry is for job_test. If no, then take no action. If
> yes, then the trigger will copy this entry in the job_history table in
> database_test. By this way, you can record all the job history when the
job
> occurred.
> The code for the trigger would be like this:
> CREATE TRIGGER [jobtest] ON [dbo].[sysjobhistory]
> FOR insert
> AS
> Declare @.job_date int
> Declare @.job_time int
> Declare @.job_idx varchar
> Set @.job_idx=' ({07C00C79-AAB0-49B0-BA0A-9E7C884440DB}'
> If @.job_idx<> (select top 1 job_id from [msdb].[dbo].[rundate] where
> job_id=@.job_idx order by rundate,runtime asc)
> --if the latest entry in the sysjobhistory is not caused by the job you
> want to record
> --no action
> Begin
> Return
> End
>
> --If the job you want to record add a new entry
> Else
> Begin
> Insert [database_test].[dbo].[job_history].[rundate] value (select top 1 *
> from [msdb].[dbo].[runtime] where job_id=@.job_idx order by rundate,runtime
> asc
> End
> Note:
> 1) This code is just a thinking, not a tested one
> 2) This method will add the workload on your system, especially on a
system
> which has many concurrent jobs.
> I hope this will help you to solve your problem. If you still have
> questions, please feel free to post message here and I am ready to help!
>
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>