asked
Limit sysjobhistory to number of executions per job
Hello, (SQL Server 2008 R2 Standard) We have an ever growing number of rows in the sysjobhistory table (1.3 million). This is preventing us from seeing the job history in Management Studio (view job history times out). I have seem some scripts which delete out rows from sysjobhistory, but I would like to have a more fine-tuned clean-up. What I would like is to specify a certain number of executions to keep in the history, and delete history rows for executions prior to that. So if I specify 10 executions, the script will keep all the rows in sysjobhistory for the most recent 10 executions for any job (regardless how many steps), and delete the rest. I have written a script which does this. It uses a TRUNCATE on sysjobhistory, so I am cautious about using it on a production server. Messing up SQL Agent is not something I want to do! I am using a TRUNCATE because a DELETE could end up deleting over a million rows, which is never nice. Could I ask for people to review the script below and make suggestions? You never know, it might be useful for you to use as well! Thanks ---------- use msdb go begin try declare @ExecutionsPerJobToKeep INT = 1000 begin transaction -- Get the rows we want to keep select d.instance_id,d.job_id,d.step_id,d.step_name,d.sql_message_id,d.sql_severity,d.message,d.run_status,d.run_date,d.run_time,d.run_duration,d.operator_id_emailed,d.operator_id_netsent,d.operator_id_paged,d.retries_attempted,d.server into #tmp from dbo.sysjobhistory d WITH(XLOCK, HOLDLOCK)-- Lock the table join ( select a.job_id, MIN(a.instance_id) instance_id from ( select h.job_id, h.instance_id, ROW_NUMBER() OVER(PARTITION BY h.Job_id ORDER BY h.instance_id desc) rowno from sysjobhistory h with(nolock) where h.step_id = 1 ) a where a.rowno = k.instance_id -- Truncate sysjobhistory, but reseed to maintain sequence in case we want to restore records from backup declare @LastIndentity int dbcc checkident('sysjobhistory') select @LastIndentity = isnull(MAX(instance_id),0)+1 from dbo.sysjobhistory truncate table dbo.sysjobhistory dbcc checkident('sysjobhistory', reseed, @LastIndentity) -- Insert the rows we want to keep set identity_insert dbo.sysjobhistory on insert dbo.sysjobhistory (instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,message,run_status,run_date,run_time,run_duration,operator_id_emailed,operator_id_netsent,operator_id_paged,retries_attempted,server) select instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,message,run_status,run_date,run_time,run_duration,operator_id_emailed,operator_id_netsent,operator_id_paged,retries_attempted,server from #tmp set identity_insert dbo.sysjobhistory off if @@TRANCOUNT > 0 commit transaction end try begin catch if @@trancount > 0 rollback transaction end catch
How many jobs do you have on this server? How are the history settings currently configured?
You might want to look at the system stored procedure [`sp_purge_jobhistory`][1] rather than doing the deletions manually. That is, at least, supported. You also ought to look at sp_set_sqlagent_properties for setting how many rows of job history are stored in total & per job; however, note that the latter is not set on a per-job basis, and so if you set this to 10, a job with ten steps will only have one real record of history, whereas a single step job will have ten items. Note also that the total isn't shared out among jobs equally, but is (I think) based on the age of the record - the oldest items get deleted... [1]:
http://msdn.microsoft.com/en-us/library/ms175044.aspx
Why won't `sp_purge_jobhistory`, appropriately wrapped up, do what you want?
You make a good point, Thomas - `sp_purge_jobhistory` will do the same sort of thing. I created a script to wrap up `sp_purge_jobhistory` in a loop to demonstrate the idea. While useful, this doesn't really gain much because 1. `sp_purge_jobhistory` essentially does a delete and does not do much else except some permission checking 2. Using `sp_purge_jobhistory` multiple times suffers from the initial problem I was hoping to avoid, which is issuing a DELETE against over a million rows. The first script using a TRUNCATE will perform faster in this situation. ---------- use msdb go declare @job_id uniqueidentifier ,@run_date int ,@run_time int ,@oldest_date datetime ,@ExecutionsPerJobToKeep int = 100 -- Get first job_id to start loop select top 1 @job_id = h.job_id from dbo.sysjobhistory h order by h.job_id while @@ROWCOUNT 0 begin -- Get run_date of oldest execution to keep. If there are not @ExecutionsPerJobToKeep exections, will use the earliest select top (@ExecutionsPerJobToKeep) @run_date = h.run_date ,@run_time = h.run_time from dbo.sysjobhistory h where h.job_id = @job_id and h.step_id = 1 order by h.run_date desc, h.run_time desc -- Call sp_purge_jobhistory if we have more than specified number of executions if @@ROWCOUNT >= @ExecutionsPerJobToKeep begin set @oldest_date = CAST(@run_date AS VARCHAR(8)) + ' ' + substring(replace(str(@run_time, 6), ' ', '0'), 1, 2) + ':' + substring(replace(str(@run_time, 6), ' ', '0'), 3, 2) + ':' + substring(replace(str(@run_time, 6), ' ', '0'), 5, 2) exec dbo.sp_purge_jobhistory @job_id = @job_id, @oldest_date = @oldest_date end -- Get the next job_id select top 1 @job_id = h.job_id from dbo.sysjobhistory h where h.job_id > @job_id order by h.job_id end
Would your purge process only delete millions of rows the first time it was run, or do you generate millions of additional rows each day? I'm thinking you could skip the whole "load the temp table, truncate, reload from tem temp table" pattern by just using delete. If it's only the first run that deletes so many rows, you can easily break that up into smaller transactions with DELETE TOP If `sp_purge_jobhistory` works for you beyond the initial delete, just use your script the first time, then use `sp_purge_jobhistory` for ongoing maintenance
14 People are following this question.
