Here is a structure that is at least very close.
There is no programmatic way to get the parameters (unfortunately). You need to format them into XML to pass in.
The Login that initiates a SQL Agent job seems to only be logged in the `message` column of `msdb.dbo.sysjobhistory`, for `step_id = 0`. This value can probably be extracted if `ORIGINAL_LOGIN()` = `NT SERVICE\SQLSERVERAGENT`.
Below is the schema and procs. The concept is to separate "init", "in process", and "completed (success or error)" logging. Records get updated via `@@SPID` and `DB_ID` and `ObjectID`. You get ObjectID to pass in from `@@PROCID`.
**Schema, Tables, and Indexes**
CREATE SCHEMA [Logging];
GO
CREATE TABLE Logging.[Status]
(
[StatusID] TINYINT NOT NULL
CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED,
[StatusName] VARCHAR(50) NOT NULL
);
GO
CREATE TABLE [Logging].[ProcessLog]
(
ProcessLogID INT NOT NULL IDENTITY(-2147483648, 1)
CONSTRAINT [PK_ProcessLog] PRIMARY KEY CLUSTERED,
DatabaseID INT NOT NULL,
ObjectID INT NULL, -- NULL = ad hoc query
SessionID SMALLINT NOT NULL
CONSTRAINT [DF_ProcessLog_SessionID] DEFAULT (@@SPID),
Step TINYINT NOT NULL, -- if you have more than 255 steps, consult psychiatrist
StatusID TINYINT NOT NULL
CONSTRAINT [FK_ProcessLog_Status]
FOREIGN KEY REFERENCES [Logging].[Status]([StatusID]),
Remarks VARCHAR(MAX) NULL, -- or maybe NVARCHAR(MAX)
Params XML NULL,
RowsSelected INT NULL,
RowsInserted INT NULL,
RowsUpdated INT NULL,
RowsDeleted INT NULL,
StartedBy [sysname] NULL,
StartedAt DATETIME2 NOT NULL
CONSTRAINT [DF_ProcessLog_StartedAt] DEFAULT (SYSDATETIME()),
UpdatedAt DATETIME2 NULL, -- use to show progress ;
StoppedAt DATETIME2 NULL
);
GO
CREATE NONCLUSTERED INDEX [IX_ProcessLog_DB_Object_SPID]
ON [Logging].[ProcessLog]([DatabaseID] ASC, [ObjectID] ASC,
[SessionID] ASC, [StatusID] ASC);
WHERE [StatusID] IN (1, 2); -- ignore closed-out entries (keeps index smaller / faster)
GO
**Stored Procedure to call at the very beginning of "logged" stored procedures**
<!-- language: lang-sql -->
CREATE PROCEDURE [Logging].[ProcessLog_Start]
(
@DatabaseID INT,
@ObjectID INT,
@Params XML
)
AS
SET NOCOUNT ON;
-- First, make sure that prior process didn't fail and skip CATCH block
IF (EXISTS(SELECT *
FROM [Logging].[ProcessLog] pl
WHERE pl.[DatabaseID] = @DatabaseID
AND pl.[ObjectID] = @ObjectID
AND pl.[SessionID] = @@SPID
AND pl.[StatusID] IN (1, 2)
))
BEGIN
UPDATE pl
SET pl.[StatusID] = 4, -- failed
pl.[Remarks] = 'Process never completed'
-- ,pl.[StoppedAt] = SYSDATETIME() -- optional set ending time
FROM [Logging].[ProcessLog] pl
WHERE pl.[DatabaseID] = @DatabaseID
AND pl.[ObjectID] = @ObjectID
AND pl.[SessionID] = @@SPID
AND pl.[StatusID] IN (1, 2); -- make sure to not update closed out entries
END;
-- Now it should be safe to create a new entry
INSERT INTO [Logging].[ProcessLog] ([DatabaseID], [ObjectID], [Step],
[StatusID], [Params], [StartedBy])
VALUES (@DatabaseID, @ObjectID, 0, 1, @Params, ORIGINAL_LOGIN());
GO
**Stored Procedure to call after all but the final step**
<!-- language: lang-sql -->
CREATE PROCEDURE [Logging].[ProcessLog_Log]
(
@DatabaseID INT,
@ObjectID INT,
@Step TINYINT,
@RowsSelected INT = NULL,
@RowsInserted INT = NULL,
@RowsUpdated INT = NULL,
@RowsDeleted INT = NULL
)
AS
SET NOCOUNT ON;
UPDATE pl
SET pl.[StatusID] = 2, -- In process
pl.[Step] = @Step,
pl.[UpdatedAt] = SYSDATETIME(),
pl.[RowsSelected] = ISNULL(@RowsSelected, pl.[RowsSelected]),
pl.[RowsInserted] = ISNULL(@RowsSelected, pl.[RowsInserted]),
pl.[RowsUpdated] = ISNULL(@RowsSelected, pl.[RowsUpdated]),
pl.[RowsDeleted] = ISNULL(@RowsSelected, pl.[RowsDeleted])
FROM [Logging].[ProcessLog] pl
WHERE pl.[DatabaseID] = @DatabaseID
AND pl.[ObjectID] = @ObjectID
AND pl.[SessionID] = @@SPID
AND pl.[StatusID] IN (1, 2); -- make sure to not update closed out entries
IF (@@ROWCOUNT = 0)
BEGIN
RAISERROR('no initial or in-process record!', 16, 1);
RETURN;
END;
GO
**Stored Procedure to call after the final step OR in a CATCH block**
<!-- language: lang-sql -->
CREATE PROCEDURE [Logging].[ProcessLog_Stop]
(
@DatabaseID INT,
@ObjectID INT,
@Step TINYINT,
@StatusID TINYINT,
@Remarks VARCHAR(MAX) = NULL,
@RowsSelected INT = NULL,
@RowsInserted INT = NULL,
@RowsUpdated INT = NULL,
@RowsDeleted INT = NULL
)
AS
SET NOCOUNT ON;
UPDATE pl
SET pl.[StatusID] = @StatusID, -- 3 = Success, 4 = Fail
pl.[Step] = @Step,
pl.[Remarks] = @Remarks,
pl.[StoppedAt] = SYSDATETIME(),
pl.[RowsSelected] = ISNULL(@RowsSelected, pl.[RowsSelected]),
pl.[RowsInserted] = ISNULL(@RowsSelected, pl.[RowsInserted]),
pl.[RowsUpdated] = ISNULL(@RowsSelected, pl.[RowsUpdated]),
pl.[RowsDeleted] = ISNULL(@RowsSelected, pl.[RowsDeleted])
FROM [Logging].[ProcessLog] pl
WHERE pl.[DatabaseID] = @DatabaseID
AND pl.[ObjectID] = @ObjectID
AND pl.[SessionID] = @@SPID
AND pl.[StatusID] IN (1, 2); -- make sure to not update closed out entries
IF (@@ROWCOUNT = 0)
BEGIN
RAISERROR('no initial or in-process record!', 16, 1);
RETURN;
END;
GO
**Demo stored procedure (input parameters are formatted as XML)**
CREATE PROCEDURE [dbo].[ProcessLogDemo]
(
@Param1 INT,
@Param2 DATETIME,
@Param3 NVARCHAR(50) = NULL
)
AS
SET NOCOUNT ON;
DECLARE @DB_ID INT = DB_ID(),
@Params XML;
SET @Params = (
SELECT @Param1 AS [Param1],
@Param2 AS [Param2],
@Param3 AS [Param3]
FOR XML PATH(N'Params')
); -- missing elements mean the value == NULL
--SELECT @Params;
EXEC [Logging].[ProcessLog_Start]
@DatabaseID = @DB_ID,
@ObjectID = @@PROCID,
@Params = @Params;
-- do something
EXEC [Logging].[ProcessLog_Log]
@DatabaseID = @DB_ID,
@ObjectID = @@PROCID,
@Step = 1,
@RowsSelected = @@ROWCOUNT;
-- do something else
EXEC [Logging].[ProcessLog_Log]
@DatabaseID = @DB_ID,
@ObjectID = @@PROCID,
@Step = 2,
@RowsUpdated = @@ROWCOUNT;
-- do final thingy
EXEC [Logging].[ProcessLog_Stop]
@DatabaseID = @DB_ID,
@ObjectID = @@PROCID,
@Step = 3,
@StatusID = 3, -- success
@RowsInserted = @@ROWCOUNT;
GO
NOTES:
* I will try to incorporate the "initiating Login" for SQL Agent jobs tomorrow.
* I realized in writing the "Demo" stored procedure that there is an even easier way of tracking the same Stored Procedure from start to finish in any particular process: create a GUID / `UNIQUEIDENTIFIER` via `NEWID()` at the very top (or it can even be generated in the "Start" stored procedure and passed back via an `OUTPUT` param) and pass that value into all of the "ProcessLog" proc calls. This will be slightly wider than the current combination of columns, but it will be less error-prone since it will be different for each process (`session_id` will not be unique in the history, hence the need to include the other columns in the look-up). A GUID is unique, and so even if the process aborts and never calls the "Stop" stored procedure, no other process will pass in the same "ProcessID". Meaning, no need for the `IF EXISTS` in the "Start" proc. I will add this column and related functionality tomorrow.
* I will add the `TRY...CATCH` construct to the "Demo" proc tomorrow.
* It might be possible to include the current TransactionID of the process as a means of cleaning up aborted entries since it is possible to query the system to see if that TransactionID is still valid (i.e. a long-running process) or if it is gone (i.e. an aborted process).