SQL Server sp purge jobhistory – To delete job history

Hello Geeks,

Beginning with SQL Server 2005, as part of the Database Maintenance Plans, MS introduces the “History Clean Up” tasks. SQL Server stored procedure SQL Server sp purge jobhistory removes the history records for a job. It returns 0 (Success) or 1 (Failure).

Syntax:

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

You can specify either job_name or job_id but not both.

You can get job_id from msdb.dbo.sysjobs table . job_id is uniqueidentifier.

If you specify @oldest_date then it deletes all his troy before this date else it will delete all job history.

• Remove History of Specific jobs:

USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory
    @job_name = N'<job name>' ;
GO

By specifying the <job name>, we can remove the history of that job.

• Remove History for all jobs:

USE msdb ;
GO
 
EXEC dbo.sp_purge_jobhistory ;
GO

• To delete history for a specific job up to specific date

USE msdb ;
GO
 
EXEC dbo.sp_purge_jobhistory
@job_name = N'<job name>' ,
@oldest_date = '<date>'

By using sp_purge_jobhistory you can remove history data older than specific date. You just need to pass the @Date as a parameter by using “sp_purge_jobhistory” provided by SQL Server.

DECLARE @Date DATETIME
 
-- Keep Last 30 days
SET @Date = GETDATE() - 30
EXEC MSDB.DBO.SP_PURGE_JOBHISTORY
@Oldest_date=@Date

By SQL Server GUI you can also purge the job history.

Step 1: Right click on SQL Server Agent and click on property.

Step 2: On the left pane select History and click check box remove agent history.
You have three options day(s), week(s) or month(s) select one of appropriate to remove agent history.

Hope this article proves helpful to you.

Regards

Arup Dolui

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook