1

We are trying to debug why SQL Agent Job history is getting deleted every 8 hours. In reading the following article, it suggests many great ideas like:

  • Create a trigger to check what application is deleting the job history

  • Check to see if we have a Job that is perhaps purging the job history

  • Check if the sp_purge_jobhistory stored procedure in msdb database is being used manually to delete the SQL Server agent job history

In the article, the trigger they created returned the following as the "Application Name" value:

  • SQLAgent - Step History Logger
  • SQLAgent - Job Manager

How would I create a trigger like this?

0

2 Answers 2

2

Here is the code to create an audit table and an audit trigger containing the information you are requesting. I have tested this and it gives me the value SQLAgent - Job Manager under the column AppName. This logs consistently when jobs run and it deletes out data in my history. (I changed it to keep 2 rows in the history to force it to delete to test.)

So the function APP_NAME() is really what you were looking for.

You can find more information related to this by BateTech on Stack Exchange.

CREATE TABLE msdb.dbo.HistoryPurgeReport 
(PurgeID INT Identity(1, 1) PRIMARY KEY, [instance_id] [int] NOT NULL, [job_id] [uniqueidentifier] NOT NULL, 
[step_id] [int] NOT NULL, [step_name] [sysname] NOT NULL, [sql_message_id] [int] NOT NULL, 
[sql_severity] [int] NOT NULL, [message] [nvarchar](4000) NULL, [run_status] [int] NOT NULL, 
[run_date] [int] NOT NULL, [run_time] [int] NOT NULL, [run_duration] [int] NOT NULL, 
[operator_id_emailed] [int] NOT NULL, [operator_id_netsent] [int] NOT NULL, [operator_id_paged] [int] NOT NULL, 
[retries_attempted] [int] NOT NULL, [server] [sysname] NOT NULL, UpdatedBy NVARCHAR(128), UpdatedOn DATETIME, 
AppName nvarchar(255), HostName nvarchar(255), ContextInfo nvarchar(255))
GO

USE MSDB

CREATE TRIGGER trg_HistoryPurgeReport ON msdb.dbo.sysjobhistory
AFTER DELETE
AS
BEGIN
    INSERT INTO HistoryPurgeReport ([instance_id], [job_id], [step_id], [step_name], [sql_message_id], [sql_severity], 
    [message], [run_status], [run_date], [run_time], [run_duration], [operator_id_emailed], [operator_id_netsent], 
    [operator_id_paged], [retries_attempted], [server], UpdatedBy, UpdatedOn, AppName, HostName, ContextInfo)
    SELECT [instance_id], [job_id], [step_id], [step_name], [sql_message_id], [sql_severity], 
    [message], [run_status], [run_date], [run_time], [run_duration], [operator_id_emailed], [operator_id_netsent], 
    [operator_id_paged], [retries_attempted], [server], SUSER_SNAME(), getdate(), APP_NAME(), HOST_NAME(), CONTEXT_INFO()
    FROM deleted
END
GO
0
0

I can't post a screenshot due to the server name being in there, but right click on SQL Server Agent and select Properties. Once there, go to the lowest option, History. Check the settings there. I don't see anything in there for hours, but it could be a combination of limiting the amount of rows in the tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.