Conrad Jagger Conrad Jagger - 2 months ago 16
SQL Question

SQL Server Agent Job History - Delete for only one day

How can we delete SQL agent job history (for a job) for just 1 day?

for e.g i just want to delete for 1 september only

Answer

sp_purge_jobhistory is provided by Microsoft to purge job history and it takes date as one of parameters.

sp_purge_jobhistory   
   {   [ @job_name = ] 'job_name' |   
     | [ @job_id = ] job_id }  
   [ , [ @oldest_date = ] oldest_date ]  

Now looking at documentation for date,it states below

The oldest record to retain in the history. oldest_date is datetime, with a default of NULL. When oldest_date is specified, sp_purge_jobhistory only removes records that are older than the value specified.

Based on above documentation,you can't delete for one day..

But looking at source code,i see all it does is check permissions and run below line

DELETE FROM msdb.dbo.sysjobhistory
 WHERE ((run_date < @datepart) OR 
 (run_date <= @datepart AND run_time < @timepart))

so you could pass ,run_date =date you need to delete

Comments