Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Abu-Dina
Posting Yak Master
206 Posts |
 Posted - 2012-07-30 : 07:29:24
|
| Hi,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. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-07-30 : 08:50:24
|
| 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. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-30 : 12:46:16
|
quote:
Originally posted by Abu-Dina
Hi,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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-08-01 : 04:25:56
|
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.
Thanks Visakh. I've changed the setting to 3 days. Hopefully this should do the trick. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-01 : 09:33:08
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-08-02 : 14:25:28
|
quote:
Originally posted by visakh16You'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 MVPhttp://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 @dtGOEXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2012-02-02T13:23:38'GOEXECUTE msdb..sp_maintplan_delete_log null,null,'2012-02-02T13:23:38' |
|
|
|
|
|
|
Job history only shows one entry for job