Free SQL Help for Devs, DBAs, and the Curious

The SQL Server Agent is an integral part of managing and monitoring SQL Server. It provides an automated way to schedule jobs, monitor SQL Server, and process alerts. However, over time, the history of these jobs can accumulate and consume significant database storage. In this blog post, we will delve into the ways that you can manage your SQL Agent History and provide some helpful cleanup scripts.

First, let us look at SQL Server 2016 and 2017. SQL Server provides a system stored procedure named sp_purge_jobhistory that can be used to delete job history. The procedure takes the job name or job id as the parameter. It can be used as follows:

-- T-SQL code to purge job history

EXEC msdb.dbo.sp_purge_jobhistory @job_name = N'MyJobName';

For MySQL 5.7, there is no built-in procedure for purging job history as there is in SQL Server. However, we can write a simple script to delete old entries from the event scheduler's history. Here is an example:

-- MySQL code to delete old events

DELETE FROM mysql.event WHERE created < DATE_SUB(NOW(), INTERVAL 1 YEAR);

In the case of DB2 11.1, we can use the ADMIN_TASK_REMOVE procedure to remove completed administrative tasks from the history. This includes both one-time and recurring tasks. Here is an example:

-- DB2 code to remove old tasks

CALL SYSPROC.ADMIN_TASK_REMOVE('MyTaskName');

If you are using Azure SQL, you can also use the sp_purge_jobhistory system stored procedure to delete job history. However, keep in mind that in Azure SQL, the SQL Agent is replaced by the Elastic Job Agent. Here is how you can use the procedure in Azure SQL:

-- T-SQL code to purge job history in Azure

EXEC msdb.dbo.sp_purge_jobhistory @job_name = N'MyAzureJobName';

As a final note, remember to consider the implications of removing job history. Although it saves storage space, it also means that you lose the ability to review past job performance and issues. Therefore, it is recommended to only remove job history that is no longer necessary for your review or audit processes.

In conclusion, managing SQL Agent History is crucial to maintain the health and performance of your SQL Server. Depending on the SQL technology you are using, you can leverage built-in stored procedures or write custom scripts to automate the cleanup process. Remember to test these scripts in a non-production environment before implementing them in a live scenario.