Clearing entries for failed jobs in the SQL agent history

Category : Scripts User Rating : 4.5 Stars      Views : 3.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.
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)

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


Comments
Post by Johne670 on Sun 08 May 2016 11:38. Report Inappropriate Post

Hello there, I discovered your website by the use of Google while looking for a related subject, your web site came up, it seems good. I have bookmarked it in my google bookmarks. dedgcfgcedda
Post by irshad md on Wed 10 Jul 2019 08:06. Report Inappropriate Post
Website : https://www.tapresume.co.in/web-developer-jobs
Nice info

Post a comment   No login required !

Name : Email : Website :
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered