Logging Agent Jobs That Call Stored Procedures

Step failed is a job-history message that leaves the real investigation unfinished. Logging Agent jobs works better when the procedure records its own attempt and error details.

Give Each Procedure Attempt an Identity

Use a new run identifier for every invocation, including retries. When logging Agent jobs, keep a separate stable business batch identifier for the work itself. An execution attempt and a business unit have different meanings.

Record the Agent job and step identifiers when the scheduler is the caller. Also record UTC start and finish times for operational analysis. Those fields connect the procedure evidence to the calling job without relying only on message text.

I start with structured error details before adding more printed output. I also keep business idempotency separate from the logging design. Better logs do not make a repeated business operation safe automatically.

The isolated example creates a run table, an event table, and a tiny work destination. Started and terminal events are separate retained rows. The destination's BatchId key represents the independent business identity.

CREATE TABLE dbo.AgentRun
(
    RunId uniqueidentifier NOT NULL PRIMARY KEY,
    BatchId int NOT NULL,
    JobId uniqueidentifier NOT NULL,
    StepId int NOT NULL,
    HistoryFloor int NOT NULL,
    StartedUtc datetime2(3) NOT NULL,
    FinishedUtc datetime2(3) NULL,
    Status varchar(12) NOT NULL,
    ErrorNumber int NULL,
    ErrorSeverity int NULL,
    ErrorState int NULL,
    ErrorProcedure nvarchar(128) NULL,
    ErrorLine int NULL,
    ErrorMessage nvarchar(2048) NULL
);
CREATE TABLE dbo.AgentEvent
(
    EventId bigint IDENTITY NOT NULL PRIMARY KEY,
    RunId uniqueidentifier NOT NULL REFERENCES dbo.AgentRun(RunId),
    EventUtc datetime2(3) NOT NULL,
    EventType varchar(12) NOT NULL
);
CREATE TABLE dbo.LoggedBatch
(
    BatchId int NOT NULL PRIMARY KEY,
    ProcessedUtc datetime2(3) NOT NULL
);
GO
CREATE OR ALTER PROCEDURE dbo.LoggedWork
    @BatchId int,
    @JobId uniqueidentifier,
    @StepId int,
    @ForceFailure bit = 0
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    IF @@TRANCOUNT <> 0
        THROW 50000, 'Invoke this procedure outside another transaction.', 1;
    DECLARE @RunId uniqueidentifier = NEWID(), @HistoryFloor int;
    SELECT @HistoryFloor = COALESCE(MAX(instance_id), 0)
    FROM msdb.dbo.sysjobhistory WHERE job_id = @JobId;
    INSERT dbo.AgentRun
        (RunId, BatchId, JobId, StepId, HistoryFloor, StartedUtc, Status)
    VALUES (@RunId, @BatchId, @JobId, @StepId, @HistoryFloor,
            SYSUTCDATETIME(), 'Started');
    INSERT dbo.AgentEvent VALUES (@RunId, SYSUTCDATETIME(), 'Started');
    BEGIN TRY
        BEGIN TRANSACTION;
        INSERT dbo.LoggedBatch VALUES (@BatchId, SYSUTCDATETIME());
        IF @ForceFailure = 1
            THROW 50001, 'Deliberate demonstration failure.', 1;
        COMMIT TRANSACTION;
        UPDATE dbo.AgentRun
        SET Status = 'Succeeded', FinishedUtc = SYSUTCDATETIME()
        WHERE RunId = @RunId;
        INSERT dbo.AgentEvent VALUES (@RunId, SYSUTCDATETIME(), 'Succeeded');
        SELECT @RunId AS RunId, 'Succeeded' AS Status;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorNumber int = ERROR_NUMBER(),
                @ErrorSeverity int = ERROR_SEVERITY(),
                @ErrorState int = ERROR_STATE(),
                @ErrorLine int = ERROR_LINE();
        DECLARE @ErrorProcedure nvarchar(128) = ERROR_PROCEDURE(),
                @ErrorMessage nvarchar(2048) = ERROR_MESSAGE();
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        UPDATE dbo.AgentRun
        SET Status = 'Failed', FinishedUtc = SYSUTCDATETIME(),
            ErrorNumber = @ErrorNumber, ErrorSeverity = @ErrorSeverity,
            ErrorState = @ErrorState, ErrorLine = @ErrorLine,
            ErrorProcedure = @ErrorProcedure, ErrorMessage = @ErrorMessage
        WHERE RunId = @RunId;
        INSERT dbo.AgentEvent VALUES (@RunId, SYSUTCDATETIME(), 'Failed');
        SELECT @RunId AS RunId, 'Failed' AS Status;
        THROW;
    END CATCH;
END;
GO

Keep Logging Agent Jobs Intact When Work Rolls Back

The start record and event are inserted before the business transaction. The error handler rolls back that transaction before writing failure evidence. An uncommittable transaction cannot safely accept the failure-log updates.

The procedure rejects an ambient caller transaction intentionally. Otherwise an outer rollback could remove supposedly retained log records. The scheduler step should call it without wrapping its entire invocation in another transaction.

A committed business change and its terminal log are separate operations here. Logging can fail after business commit. Reconcile the stable business batch with the run record. A Failed status does not always mean that business work rolled back.

The example insert is intentionally not a complete idempotent processing design. A repeated completed BatchId raises a key conflict. Define safe replay behavior for the real business operation before enabling automatic retries.

Create a Disabled Demonstration Job

The next script creates an unscheduled, disabled job for the isolated database. It does not start the job or attach a schedule. Use a supported SQL Server Agent instance with appropriate administration rights.

The procedure needs approved visibility of the relevant msdb history rows. Review that permission under the job's actual execution context. Do not solve missing visibility by broadly promoting the production application login.

The captured JobId is embedded into the job's fixed command when the step is created. That avoids guessing an identifier from the displayed job name. The step passes its known step number and chosen sample business batch.

DECLARE @WorkDatabase sysname = DB_NAME();
DECLARE @JobId uniqueidentifier, @Command nvarchar(max);
EXEC msdb.dbo.sp_add_job
    @job_name = N'Logged Procedure Demo',
    @enabled = 0, @job_id = @JobId OUTPUT;
SET @Command =
    N'EXEC dbo.LoggedWork @BatchId = 42, @JobId = N''' +
    CONVERT(nvarchar(36), @JobId) +
    N''', @StepId = 1, @ForceFailure = 1;';
EXEC msdb.dbo.sp_add_jobstep
    @job_id = @JobId, @step_id = 1,
    @step_name = N'Run logged procedure', @subsystem = N'TSQL',
    @database_name = @WorkDatabase, @command = @Command,
    @retry_attempts = 0, @on_success_action = 1, @on_fail_action = 2,
    @output_file_name = N'C:\SqlAgentOutput\LoggedJob.txt', @flags = 2;
EXEC msdb.dbo.sp_add_jobserver @job_id = @JobId, @server_name = N'(LOCAL)';
SELECT @JobId AS CreatedJobId;

The command requests a deliberate failure inside the business transaction. Its rollback should retain the run and failure event but not the sample business row. A direct call with @ForceFailure = 1 behaves that way: the Failed run and both events stay, and LoggedBatch stays empty. Change that test parameter only within the reviewed demonstration workflow.

The supplied scripts specify expected behavior without claiming a completed Agent execution. Manually test the unscheduled job in isolation when ready. Inspect the log tables, job history, and output file together after it finishes.

Configure Appended Output Logging for Agent Jobs

The output-file path is on the SQL Server machine, not necessarily the client computer. Its directory must already exist with appropriate service-account access. Validate the actual writer identity and Windows permissions before testing.

Flag two requests append behavior for the demonstration step output. Without that choice, output-file behavior defaults to overwriting. Appending preserves earlier attempt messages until the approved rotation process handles them.

Operating-system output files for T-SQL job steps require the applicable sysadmin-owned execution arrangement. Lower-privilege Agent users can use supported table output instead. Choose a permitted configuration rather than adding elevated rights solely for a file.

Printed text and result sets can contain private values. Keep diagnostic output concise and protect the file's readers. File rotation and table retention should preserve enough evidence without accumulating unlimited sensitive detail.

Join Procedure Evidence with History Candidates

Agent history has no field containing this procedure's RunId. A direct join on job name or job identifier alone multiplies unrelated executions. The example records a history watermark before each invocation to narrow candidate step rows.

The following query selects the first matching step history row after that watermark. It is a correlation candidate, not a universal exact-attempt guarantee. Retained procedure events and the printed RunId remain the attempt's direct evidence.

SELECT R.RunId, R.BatchId, R.StartedUtc, R.FinishedUtc,
       R.Status AS ProcedureStatus, R.ErrorNumber, R.ErrorProcedure,
       R.ErrorLine, R.ErrorMessage,
       J.name AS JobName,
       H.instance_id AS CandidateHistoryId,
       H.run_status AS CandidateAgentStatus,
       H.run_date, H.run_time, H.run_duration,
       H.message AS CandidateAgentMessage
FROM dbo.AgentRun AS R
LEFT JOIN msdb.dbo.sysjobs AS J ON J.job_id = R.JobId
OUTER APPLY
(
    SELECT TOP (1) SH.instance_id, SH.run_status,
           SH.run_date, SH.run_time, SH.run_duration, SH.message
    FROM msdb.dbo.sysjobhistory AS SH
    WHERE SH.job_id = R.JobId AND SH.step_id = R.StepId
      AND SH.instance_id > R.HistoryFloor
    ORDER BY SH.instance_id
) AS H
ORDER BY R.StartedUtc DESC;

SELECT E.RunId, E.EventUtc, E.EventType
FROM dbo.AgentEvent AS E ORDER BY E.EventId;

History rows appear after the relevant step completes. A currently running attempt can therefore have no candidate yet. Step zero represents job summary history and is intentionally excluded by the recorded step identifier.

Retries, manual procedure calls, history cleanup, and later executions can weaken a watermark-only association. Review those conditions before treating the candidate as exact. Persist an explicitly verified association when exact long-term correlation is required.

Agent history dates and times use their documented local integer formats. Procedure timestamps here use UTC. Do not compare those values directly without a defined conversion and time-zone policy.

Run_duration is encoded as hours, minutes, and seconds rather than raw elapsed seconds. It can also contain more than twenty-four hours. Decode it deliberately when building a duration report.

Reconcile Missing Terminal Events

Connection loss or cancellation can bypass ordinary CATCH handling. A retained Started record therefore does not prove that the work is still running. Compare scheduler state, active execution evidence, and business batch completion before classifying it.

A failure to write the initial log can prevent business work from starting. That is an intentional dependency in this simple pattern. Decide whether the real workload should fail closed when its required audit evidence is unavailable.

Protect the evidence tables from application edits unrelated to the logging interface. Changes to error records undermine their diagnostic meaning. Administrative retention should remain separate from the procedure's business transaction.

Which identifier should the operator give the next person investigating the failure? Use RunId for the attempt and BatchId for the business work. Keeping both prevents repeated attempts from becoming one confusing incident record.

Make Logging Agent Jobs Explain the Next Failure

I use logging Agent jobs to preserve facts while the error context still exists. I review failure propagation with the calling step as carefully as the CATCH block. A three a.m. message should offer more than a shrug in capital letters.

Keep the procedure's rethrown error visible to Agent. Catching and printing it without failure propagation can falsely mark a broken step successful. Review the actual terminal status in both the procedure evidence and scheduler history.

Test deliberate failure, success, duplicate business batch, and interrupted execution in isolation. Confirm retained events and permitted output access for each path. A logging pattern becomes dependable through those checks rather than through the number of columns in its table.

Related reading on this blog: Agent Jobs Running Longer Than Usual: Finding Them in Job History and Documenting SQL Agent Jobs: Schedules, Steps and Owners in One Query.

Job history is not the whole incident record, it is scheduler evidence connected to a durable procedure attempt.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.