After I’ve installed a new SQL agent job, I usually run it a few times to check that it runs correctly. Sometimes the agent job
will fail the first few times if I’ve got something wrong or not configured it correctly. Once it’s up and running correctly I
like to clear out any failed entries from the log to avoid any confusion and to start with a clean sheet.
The following script will clear out failed entries for a specified job from the Job History log before a specified date
(to view the Job History log right click on the job and select ‘View History’).
I recommend running the query as a SELECT and inspecting the result before running the DELETE.
--
********************************************************************
-- Description : This script will
delete rows for failed jobs in the
-- SQL Server agent
log, so
-- that they don't
appear in the 'View History' log.
-- The rows which will
be deleted are where :
-- The job is named
@JobName
-- AND the job has
failed
-- AND the failure
was been since date @EarliestDate
-- Author/Owner : SQL Matters
--
********************************************************************
USE msdb
GO
DECLARE @JobName VARCHAR(MAX)
DECLARE @EarliestDate DATETIME
-----------------------------------------------------------------------
-- *** User Customisation - Change
values of variables here to
-- delete different rows.
-- Set the name of the job e.g. 'Load
Database'
SET @JobName='Load Database'
-- Set the earliest date to be deleted
e.g. '21 Dec 2010 09:00:00'
SET @EarliestDate='21 Dec 2010 09:00:00'
-----------------------------------------------------------------------
-- Run the query with the SELECT rather
than the DELETE first,
-- to check which rows will be deleted
before doing the delete.
--DELETE sysjobhistory
SELECT *
FROM sysjobhistory SJH
JOIN sysjobs SJ
ON SJH.job_id = sj.job_id
WHERE run_status = 0 -- i.e. failed
AND name = @JobName
AND CONVERT(VARCHAR,run_date) + RIGHT('0'+CONVERT(VARCHAR,run_time),6) >=
CONVERT(VARCHAR,@EarliestDate,112) +
LEFT(REPLACE(CONVERT(VARCHAR,@EarliestDate,114),':',''),6)
Related Articles
The following articles may also be of interest :
Link back to this article :
https://www.sqlmatters.com/Articles/Clearing entries for failed jobs in the SQL agent history.aspx
Keywords
Scripts, SQL Agent