Tuesday, March 19, 2013

Purge SQL Server Job History

The msdb database stores SQL Server job information, including the job and job steps history. The job history information can grow really fast, especially if you have a lot of SQL Server jobs which run frequently. In turn, this can cause the msdb database to grow in size rather quickly. It is a good idea to purge old SQL Server job history on regular basis. You might want to check with your company policy or legal team on how much of job history you need to keep.

How do you go about purging the SQL Server Job History? There are several ways in which you can do this.

sp_purge_jobhistory

The sp_purge_jobhistory stored procedure in msdb database can be used to delete the SQL Server agent job history. It accepts 3 input parameters:

  • (Optional) @job_id - the SQL Server job id of the job history that you want to delete

  • (Optional) @job_name - the SQL Server job name of the job history that you want to delete

  • (Optional) @oldest_date - delete SQL Server job history that is older than the @oldest_date

One thing to know is that when running the sp_purge_jobhistory, you can specify the job_id or job_name or neither of them. But you can not specify both the job_id and job_name. If you do, you will most likely get the following error message:

"Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 25
Supply either @job_id or @job_name to identify the job."

Some sample usage:

To delete all job history:

exec msdb.dbo.sp_purge_jobhistory;

To delete all job history of SQL Server job named "Backup TestDB":

exec msdb.dbo.sp_purge_jobhistory @job_name = 'Backup TestDB';

Alternatively:

declare @job as uniqueidentifier;
set @job = (select job_id from dbo.sysjobs where [name] = 'Backup TestDB');
exec msdb.dbo.sp_purge_jobhistory @job_id = @job;

To delete job history that is older than 30 days:

declare @jobdate as datetime;
set @jobdate = dateadd(dd, -30, getdate());
exec msdb.dbo.sp_purge_jobhistory @oldest_date = @jobdate;

To delete job hisotry for SQL Server job named "Backup TestDB", which are older than 30 days:

declare @jobdate as datetime;
set @jobdate = dateadd(dd, -30, getdate());
exec msdb.dbo.sp_purge_jobhistory @job_name = 'Backup TestDB', @oldest_date = @jobdate;

It is pretty straight forward. To do this on regular basis, you can create a SQL Server agent job to run the sp_purge_jobhistory with the appropriate input parameter(s) on a scheduled time.

SQL Server Agent Properties

If you go to the SQL Server Agent Properties window, you have the option to "remove agent history". Here, you need to specify the cut-off time.

agent_property

If you try to script this out, you will see that it is generating the following script:

EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2013-02-12T22:54:55'

So basically it is executing the same sp_purge_jobhistory stored procedure on the background. One thing to remember about purging the job history this way is that it is just one time only. It will not actually create any SQL Server Agent that would automatically delete job history that are older than 4 weeks.

syspolicy_purge_history SQL Server Job

By default, if you are using SQL Server 2008, 2008 R2 or 2012, you might see the syspolicy_purge_history SQL Server agent job. The job is scheduled to run daily at 2 AM. This SQL Server job would look into the Policy Management "HistoryRetentionInDays" property (You can get to this window by going to Management on the Object Explorer and then right click Policy Management and select Properties).

policy_mgmt

By default, the HistoryRetentionInDays is set to 0, which means that it will not delete any SQL Server job history. If you set it 30, then on daily basis at 2 AM, it will delete SQL Server job history that are older than 30 days.

3 comments:

  1. Thank you very much!
    Help me a lot!!!!

    ReplyDelete
  2. Hi Lucas,

    Thanks for the above information. Could you please let me know if i can improve my below query.

    Requirement: I would like to remove job history depends on below points.
    * will keep records of last 18 days
    * We will not remove history data of jobs which has rows below 2000

    declare @od datetime;
    declare @jobn sysname;
    set @od=DATEADD(DD,-17,GETDATE());
    set @jobn=(select sj.name from sysjobs sj inner join sysjobhistory sjh
    on sj.job_id=sjh.job_id group by sj.name having count(*)>2000);
    exec sp_purge_jobhistory @job_name=@jobn,@oldest_date=@od

    Could you please help here

    ReplyDelete
    Replies
    1. Looking at the query that you have, it seems that it might fail if there are multiple jobs that have job history of more than 2,000. One possibility:

      DECLARE
      @od AS DATETIME,
      @i AS INT,
      @j AS INT;

      SET @od = DATEADD(DD, -17, GETDATE());

      CREATE TABLE #JobsToBeDeleted (Id INT IDENTITY PRIMARY KEY, JobId UNIQUEIDENTIFIER);

      INSERT INTO #JobsToBeDeleted
      SELECT DISTINCT job_id
      FROM dbo.sysjobhistory
      GROUP BY job_id
      HAVING COUNT(*) > 2000;

      SET @i = 1;
      SET @j = (SELECT ISNULL(MAX(Id), 0) FROM #JobsToBeDeleted);

      WHILE (@i <= @j)
      BEGIN
      DECLARE @JobId AS UNIQUEIDENTIFIER;
      SET @JobId = (SELECT JobId FROM #JobsToBeDeleted WHERE Id = @i);

      IF (@JobId IS NOT NULL)
      BEGIN
      EXEC sp_purge_jobhistory @job_id = @JobId, @oldest_date = @od;
      END

      SET @i += 1;
      END

      DROP TABLE #JobsToBeDeleted;

      Also, not sure if you know this, if you go to the SQL Server Agent properties and then go to History page, there is a setting where you can also limit the job history (in rows). You can specified the maximum job history row per job. But then it would not guarantee that they would keep records of last 18 days (assuming if there are jobs that create lots of history).

      Delete