SQL Server 2012 – Trigger to Find Deleting Job Agent History

sql serversql-server-2012sql-server-agenttrigger

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?

Best Answer

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
Related Question