July 4, 2012 at 4:19 am
Hi,
i have my job running every 5 min. I want to view the history log. In job history it shows only few hundreds of records.. But i want to see from begining. Can anyone tell me, where to view whole log of job history??
Thanks in Advance. 🙂
Regards,
Jigneshal
" Stretch Your Limit Little Beyond Your Limit....! "
😎
July 4, 2012 at 4:24 am
The job history table is a cumulative table of all history from all jobs. By default it only stores around 1000 rows in the table, so if you have lots of frequently executing jobs with lots of steps then you soon start to see old history records being purged as it requires the space for new records.
You can change this value by modifing the SQL Agent properties to store more rows or delete after a certain number of days, or if you wanted never empty the history table, but be warned about excessive disk space usage for MSDB when you do that.
The information you want is in MSDB.dbo.sysjobhistory, you will need to link it to other sysjob.......... tables to make sence of the job id's etc.
July 4, 2012 at 5:18 am
anthony.green (7/4/2012)
The job history table is a cumulative table of all history from all jobs. By default it only stores around 1000 rows in the table, so if you have lots of frequently executing jobs with lots of steps then you soon start to see old history records being purged as it requires the space for new records.You can change this value by modifing the SQL Agent properties to store more rows or delete after a certain number of days, or if you wanted never empty the history table, but be warned about excessive disk space usage for MSDB when you do that.
The information you want is in MSDB.dbo.sysjobhistory, you will need to link it to other sysjob.......... tables to make sence of the job id's etc.
Thanks... Anthony...
Regards,
Jigneshal
" Stretch Your Limit Little Beyond Your Limit....! "
😎
July 4, 2012 at 8:25 am
If you want to modify this, you can run the below T-SQL. The first changes the max rows to 2000, max rows per job to 200 (as opposed to the default 100):
use msdb;
go
exec msdb.dbo.sp_set_sqlagent_properties
@jobhistory_max_rows=2000,
@jobhistory_max_rows_per_job=200;
go
July 4, 2012 at 9:02 am
Be aware that the option to purge data after a set time in the agent properties is a one-time manual process.
That is, if you uncheck the option to manage the history using a set row count and check the option to keep xx days, weeks months it will run the purge history process that one time only.
What I do to manage the history is to uncheck the option to manage by row size and create a maintenance plan that runs the History Cleanup task (or an agent job that runs the stored procedure). I set the History Cleanup task to keep 6 months of history - which allows me to trend database size from the backup size.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply