CDC Agent Job fills up the Agent Jobs History
-
We have implemented CDC on one of our databases. We now find that the job history for the CDC capture job appears to be forcing out the job history records for our other agent jobs.
We have set the Maximum job history log size to 12,000 and the max job history rows per job to 200. What we're seeing is that the CDC capture job appears to ignore the 200 rows limit and currently has around 10,000 rows in the msdb.dbo.sysjobhistory table. The other jobs are able to create up to 200 rows each, but we are seeing that these are being purged regularly during the day and so we are losing historical data. So we are keeping the 200 rows for job which are running every few minutes, but those jobs that run daily are having their records purged.
I've hunted around but can't find any jobs that are manually purging the job history table.
I'm assuming that the 200 row limit (max rows per job) is not being applied to the CDC job, because this is actually a continuously running job, with each log entry relating to the step being run repeatedly. If I understand correctly, when a job runs it writes to the log history table. Only once it has written the records out will it then check to see if it now exceeds the "max rows per job" limit and if so will purge some of it's own records. Presumably, as the CDC job never completes, it never purges it's own records.
What I'm not clear about is how the "Maximum job history log size" limit is enforced. I'm assuming that whatever task is doing this is able to delete records relating to any job, irrespective of the "max rows per job" limit. If so, then it would make sense that the CDC job creating so many records would create a problem as this is resulting in records belonging to other jobs which are over a certain number of hours old being deleted. I'm assuming that whatever cleanup task is enforcing the "log size" limit deletes the oldest records first.
So (finally) my question is - has anyone else encountered issues with the CDC Capture jobs filling up the job history tables and so resulting in the removal of history records for other jobs. And if so, have they found a simple solution to this (other than creating a job to manually purge the CDC Capture job history records)?
-
This is just a guess... but have you tried turning off this configuration? The "Include step output in history" option? Shown here as enabled - I think that might be the default...
Thomas Rushton
blog: https://thelonedba.wordpress.com -
That option is not selected so it can't be that.
-
===EDIT=== Sorry, I missed the part about "other than set up a job". As far as I know, there is no other way.
Setup a job specifically to cleanup the history for the capture job.
Example:
-- Set @dt as required. This example deletes all history older than one hour.
DECLARE @dt datetime = DateAdd(hour, -1, GetDate())-- Assume database is MyDatabase and the capture instance is cdc.MyDatabase_capture
EXEC msdb.dbo.sp_purge_jobhistory @job_name = 'cdc.MyDatabase_capture', @oldest_date = @dt; -
Yes, that was the conclusion we came to. I was just curious if there was any other way of resolving this.
Thanks.
-
I've observed the same issue - and appreciate having this post to help me along.
Um - however I don't think using the system stored procedure msdb.dbo.sp_purge_jobhistory will work for cdc jobs - because the run_date and run_time values being logged are not accurate (then same values are logged after the job starts being continuous).
So I've put together the following, because I want to retain the most recent cdc job history (up to the configured max history per job). Warning - I'm making use of msdb system stored procedures, and directly purging rows from the job history table - so future SQL version updates may impact this (tested against SQL 2016). Enjoy
IF OBJECT_ID('tempdb.dbo.#sp_Get_sqlagent_properties') IS NOT NULL
DROP TABLE #sp_Get_sqlagent_properties
CREATE TABLE #sp_Get_sqlagent_properties
(
auto_startvarchar(1000)
,msx_server_namevarchar(1000)
,sqlagent_typevarchar(1000)
,startup_accountvarchar(1000)
,sqlserver_restart varchar(1000)
,jobhistory_max_rows varchar(1000)
,jobhistory_max_rows_per_job varchar(1000)
,errorlog_file varchar(1000)
,errorlogging_level varchar(1000)
,error_recipient varchar(1000)
,monitor_autostart varchar(1000)
,local_host_server varchar(1000)
,job_shutdown_timeout varchar(1000)
,cmdexec_account varchar(1000)
,regular_connections varchar(1000)
,host_login_name varchar(1000)
,host_login_password varchar(1000)
,login_timeout varchar(1000)
,idle_cpu_percent varchar(1000)
,idle_cpu_duration varchar(1000)
,oem_errorlog varchar(1000)
,sysadmin_only varchar(1000)
,email_profile varchar(1000)
,email_save_in_sent_folder varchar(1000)
,cpu_poller_enabled varchar(1000)
,alert_replace_runtime_tokens varchar(1000)
)
insert into #sp_Get_sqlagent_properties
exec
msdb.dbo.sp_Get_sqlagent_properties
;with cdc_job_history
(
job_name
,job_id
,job_history_row_num
,instance_id
,jobhistory_max_rows_per_job
)
AS
(
SELECTj.name
,j.job_id
,ROW_NUMBER() OVER (PARTITION BY h.job_id ORDER BY h.instance_id DESC)AS job_history_row_num
,h.instance_id
,a.jobhistory_max_rows_per_job
FROMmsdb.dbo.sysjobhistoryh
INNER JOINmsdb.dbo.sysjobsj
ONj.job_id= h.job_id
ANDj.namelike 'cdc%'
CROSS APPLY(select jobhistory_max_rows_per_job from #sp_Get_sqlagent_properties)a (jobhistory_max_rows_per_job)
)
DELETEhFROMmsdb.dbo.sysjobhistoryh
INNER JOINcdc_job_historyc
ONc.instance_id= h.instance_id
WHEREc.job_history_row_num> c.jobhistory_max_rows_per_job
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply