Conrad Jagger Conrad Jagger - 1 year ago 166
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 Source

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

   {   [ @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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download