We have a problem where only the latest entry is displayed in the job history for a particular job. There are 14 jobs that run once day.
I've checked the history settings for the SQL Server Agent and it's configured to store a maximum of 1000 rows for all jobs and 100 row per job. The jobhistory table is full. I've increased the maximum size to 5000 rows but we're only interested in keeping the last three days’ worth of job history. Should I create another job to delete entries for this particular job that are more than 3 days old?
I always copy job history into a table in another database and run that every hour or so. Often want to see what happened several days ago. Also shows yoou when someone changed a job.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
We have a problem where only the latest entry is displayed in the job history for a particular job. There are 14 jobs that run once day.
I've checked the history settings for the SQL Server Agent and it's configured to store a maximum of 1000 rows for all jobs and 100 row per job. The jobhistory table is full. I've increased the maximum size to 5000 rows but we're only interested in keeping the last three days’ worth of job history. Should I create another job to delete entries for this particular job that are more than 3 days old?
Thanks.
You've property in SQL server agent which enables you to determines till what times history you need to keep. Its accessed by right clicking on sql server agent icon on object explorer in ssms and choosing properties. Select History tab and see remove agent history property. you can configure it to remove history older than set value of days\weeks from this.
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
You've property in SQL server agent which enables you to determines till what times history you need to keep. Its accessed by right clicking on sql server agent icon on object explorer in ssms and choosing properties. Select History tab and see remove agent history property. you can configure it to remove history older than set value of days\weeks from this.
Thanks Visakh. I've changed the setting to 3 days. Hopefully this should do the trick.
quote:Originally posted by visakh16 You've property in SQL server agent which enables you to determines till what times history you need to keep. Its accessed by right clicking on sql server agent icon on object explorer in ssms and choosing properties. Select History tab and see remove agent history property. you can configure it to remove history older than set value of days\weeks from this.
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
Unfortunately - that property is not one that is persisted. In fact, using that property just runs the history cleanup at the time you click OK on the box.
The way to manage this is to remove the setting for number of rows (uncheck the box) - then setup a maintenance plan with the History Cleanup task in that plan. Set this up to run daily and set the properties of the task to keep as much history as you want.
If you don't like maintenance plans - you can view the t-sql from the history task and build your own...here is an example of the code from that task:
declare @dt datetime select @dt = cast(N'2012-02-02T13:23:38' as datetime) exec msdb.dbo.sp_delete_backuphistory @dt
GO
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2012-02-02T13:23:38'
GO
EXECUTE msdb..sp_maintplan_delete_log null,null,'2012-02-02T13:23:38'
Job history only shows one entry for job
Posted - 07/30/2012 : 07:29:24
