August 10, 2010 at 2:39 pm
Hello,
I have created a view joining sysjobs, sysjobhistory, sysjobactivity tables to log the execution of jobs running through SQL server agent. But it looks like the records in sysjobactivity etc. get deleted after each session(i.e. when sql agent starts). How do I CONTROL THAT, so that I can have a log for the jobs executed in the last 1 year or so?
If that is not possible, is there a better way I can do this?
August 10, 2010 at 11:41 pm
SSIS.COM (8/10/2010)
Hello,I have created a view joining sysjobs, sysjobhistory, sysjobactivity tables to log the execution of jobs running through SQL server agent. But it looks like the records in sysjobactivity etc. get deleted after each session(i.e. when sql agent starts). How do I CONTROL THAT, so that I can have a log for the jobs executed in the last 1 year or so?
If that is not possible, is there a better way I can do this?
Yes there is. Right click SQL Server Agent and select properties. Select the History page and it is there you can configure how much history to keep.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
August 11, 2010 at 9:42 am
I have tried with 100000 total rows and 10000 rows for each job and selected the keep history option and changed to 52 weeks. But, once I click Ok and g oto history again it just shows up the default settings....
So, What might be the problem?
Is there an option where I can ask SQL Server to not flush the records after the session is closed?
August 11, 2010 at 10:37 am
SSIS.COM (8/11/2010)
I have tried with 100000 total rows and 10000 rows for each job and selected the keep history option and changed to 52 weeks. But, once I click Ok and g oto history again it just shows up the default settings....So, What might be the problem?
Is there an option where I can ask SQL Server to not flush the records after the session is closed?
Firstly though, went you saved and returned to the "history" page you should see the saved settings for "max job history log size" and "max job history rows per job" retained. In you case 100000 log size and 10000 job history rows.
Now for "remove agent history", this property is actually real-time and doesn't save. When you set the "older than" option and clicked OK SSMS actually deleted what you specified in real-time (Go Figure). This is a real-time cleanup option and not a configurable item for saving. IT IS AS DESIGN!!!!!! So you can save job history by specifying "max job history log" and "max rows per job" but you will have to use SP_PURGE_JOBHISTORY to control the cleanup in code or by manually setting "remove agent history" property and doing it once each time.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
August 11, 2010 at 1:26 pm
Thanks for ur reply mostinteresting man(dos equis)....:-)
what do you mean by manually setting the "remove agent history" if it is a relatime update( u r rite it is for real time update)...The job history is already flushed out when SQL agent started so there are no records basically if I just restarted my SLQ agent
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply