January 24, 2016 at 4:59 am
Hi
I Enabled CDC for 1 table about 10 Day ago.
After that my job history became weeker and weeker. to this time every job has about 1 or 2 rows for history.
I increased Job history limitation , But It didn't work.
Today I found this query to list Job Histories:
SELECT j.name, COUNT(*) Executions
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
GROUP BY j.name
ORDER BY Executions DESC
And this is result :
name -------------------Executions
cdc.DBName_capture------------- 4950
DBName_Backup.Subplan------------- 17
DBName__SendEmailOnLowSpace------------- 5
DBName_Transfer_LogData_To_DB_Logs------------- 3
DBName_Denormal_CategoryLevels------------- 1
Then the new job related to cdc jet most of my job history capacity.
what do i do ? (Else of increase and increase Job History limitation)
Thank you
January 24, 2016 at 9:27 am
What does this return for jobhistory_max_rows and jobhistory_max_rows_per_job?
EXEC msdb.dbo.sp_get_sqlagent_properties
At any rate, it sounds like you will just have to figure out how you want to handle the frequent-running CDC job.
1) You could just remove limits on job history altogether. I typically don't like doing this, since the logs for very frequent jobs can get quite unwieldy very quickly.
2) You could set the jobhistory_max_rows_per_job to some number suitably lower than job_history_max_rows, so that the frequent running job doesn't push all the other jobs out of history.
I usually opt for 2. You can do that by increasing max_rows, decreasing max_rows_per_job, or both; the goal is to leave enough of a gap between max_rows and max_rows_per_job to have history for your other jobs. The exact numbers you pick will depend on how much history you want/need to keep for each job.
For an example, if the CDC job runs 1500 times a day, and your max_rows and max_rows_per_job are both set to 5000, the problem is that after a few days the CDC job will push nearly all the executions of daily and weekly jobs out of history. If you only want/need to see CDC job history for a couple days, then you could set max_rows_per_job to 3000. That would leave 2000 rows of history for your other jobs.
Again, this is just an example, and the exact numbers you pick will have to be based on your requirements.
Cheers!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply