Skip to content
Frequent Agent is a SQL Server Stored Procedure that will check for frequently running SQL Agent Jobs move their Agent History from MSDB to the JobHistory_Archive table which is created inside the DBA_Tasks database and then purge the data from the MSDB database
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
README.md
frequent_agent_check.sql

README.md

Frequent Agent

Frequent Agent is a SQL Server Stored Procedure that will check for frequently running SQL Agent Jobs move their Agent History from MSDB to the JobHistory_Archive table which is created inside the DBA_Tasks database and then purges the data from the MSDB database.

In our enviroment we found that we had some jobs that were running daily every 30 seconds or so which was bulking out the sysjobhistory table in MSDB and when the default purge settings were enabled this was causing other job history to be cleared out, so investigating why a backup had failed for example was becoming problomatic.

Requirements

  • Ability to create stored procedures
  • Ability to create tables
  • Ability to drop stored procedures
  • A database with the name DBA_Tasks
  • Schema in the above mentioned database called DBA
  • SQL Server 2008+

When running this script if the an object exists in the DBA_Tasks database under the schema DBA with the name p_Cleanup_Frequent_Job_History it will be dropped and this stored procedure created in it's place.

Further Reading

This stored procedure makes use of purge_jobhistory we only specify the job name, no date range is specified for the high frquency jobs as in our instance we didn't want to keep any of this data in MSDB at all.

You can’t perform that action at this time.