Sql-Server-2014

儲存過程處理和錯誤日誌

  • May 25, 2020

我曾經在一家擁有第三方數據倉庫解決方案的公司工作。顯然，所有對象和表都隱藏在支持數據庫中，所以我不清楚某些儲存過程中到底發生了什麼。我在那裡看到了這個有趣的儲存過程，並想在我自己的解決方案中複製它，但我無法理解它是如何工作的。我正在描述下面的儲存過程，如果有人能給我一些關於如何實現這一點的想法，那將非常有幫助。如果你能建議我如何讓它變得更好，那就更好了。

儲存過程稱為程序日誌。它具有 DBID、ObjectId、Step、Status、Remarks、Reads、Inserts、Updates、Delete 等參數

我們要做的是，在每個儲存過程中，我們必須執行這個狀態為 2 (In Progress) 的儲存過程。的可變步長。基於 Insert update select 和 delete 語句的行數，我們應該在各自的儲存過程參數變數中記錄值。最後，您可以執行狀態為 3（已完成）的相同儲存過程，或者如果該過程以 catch 塊結束，則在備註部分中狀態將為 4（失敗），我們可以複製 SQL 的錯誤消息。

要查看所有這些資訊，我們可以訪問報告，顯然我沒有原始碼，但報告顯示了儲存過程在完成時開始的時間，狀態是多少插入更新刪除並讀取它做過。如果失敗，錯誤資訊是什麼？

我已經沒有什麼改進商店的想法了，誰開始的？參數的值是多少？對於誰開始儲存過程部分，我很困惑。大多數這些儲存過程作為不同作業的一部分執行。我們所有的作業都作為服務帳戶使用者執行，但作業是由不同的使用者手動啟動的。我需要找出哪個使用者啟動了它，就像在儲存過程中一樣，作為目前使用者，它總是會顯示服務帳戶。同樣對於參數值，是否有更好的動態方法來找出這一點？而不是手動設置變數的值。我想使用 INPUTBUFFER 的輸出，但它只顯示參數的名稱而不是值。

如果有人可以指導我有關此審計 SP 的後端表結構和腳本，那將非常有幫助。也歡迎任何更多的改進想法。

我的主要困惑：我相信他們有一些儲存這些儲存過程值的表，如果 SP 已經在執行，他們確實在記錄中更新然後執行插入，但他們如何確定在場景中執行插入而不是更新其中儲存過程嚴重失敗並且未執行 catch 塊。

這是一個至少非常接近的結構。

沒有程式方式來獲取參數（不幸的是）。您需要將它們格式化為 XML 才能傳入。

啟動 SQL 代理作業的登錄似乎只記錄在 , 的message列msdb.dbo.sysjobhistory中step_id = 0。可以提取此值，但不能在作業執行期間提取。

你得到 ObjectID 從@@PROCID.

下面是架構（2 個表）和儲存過程（3 個過程）。這個概念是將“init”、“in process”和“completed (success or error)”日誌分開。這允許僅在適當的時間設置某些列（例如，只需要在開始時設置DatabaseID,StartedAt等）。分離事件的類型還可以更容易地擁有特定於事件的邏輯（是的，即使在單個 proc 中也可以擁有它，但是當您只需要每個事件類型的一個子集時，您仍然擁有所有輸入參數）。

“程序”記錄通過其 IDENTITY（和集群 PK）值進行更新。這是具有“事件類型”分離的另一個好處：它可以更容易地處理擷取SCOPE_IDENTITY()並將其傳遞回以用於其他兩個日誌記錄儲存過程。如果儲存過程失敗並且沒有進入CATCH塊，則無需擔心意外更新該過程記錄，因為下次任何儲存過程（正在記錄的）啟動時，它將獲得一個新的/唯一的 ID更新。

清理（​​可選）和架構

/* -- optional cleanup
DROP PROCEDURE [dbo].[ProcessLogDemo];

DROP PROCEDURE [Logging].[ProcessLog_Log];
DROP PROCEDURE [Logging].[ProcessLog_Start];
DROP PROCEDURE [Logging].[ProcessLog_Stop];

DROP TABLE [Logging].[ProcessLog];
DROP TABLE Logging.[Status];

DROP SCHEMA [Logging];
*/

CREATE SCHEMA [Logging];
GO

表和索引

CREATE TABLE Logging.[Status]
(
 [StatusID] TINYINT NOT NULL 
             CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED,
 [StatusName] VARCHAR(50) NOT NULL
);

CREATE TABLE [Logging].[ProcessLog]
(
 ProcessLogID  INT NOT NULL IDENTITY(-2147483648, 1) -- start at INT min value
                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 NVARCHAR(MAX) NULL, -- or maybe VARCHAR(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 / "heartbeat"
 StoppedAt DATETIME2 NULL
);
GO

在“記錄”儲存過程的最開始呼叫的儲存過程

CREATE PROCEDURE [Logging].[ProcessLog_Start]
(
 @DatabaseID INT,
 @ObjectID INT,
 @Params XML,
 @ProcessLogID INT = NULL OUTPUT
)
AS
SET NOCOUNT ON;

-- First, capture the MAX "instance_id" from sysjobhistory if this process is a SQL
-- Server Agent job (use later to get the "invoked by" Login), else grab the Login.
DECLARE @StartedBy [sysname];

IF (EXISTS(
          SELECT *
          FROM   sys.dm_exec_sessions sdes
          WHERE  sdes.[session_id] = @@SPID
          AND    sdes.[program_name] LIKE N'SQLAgent - TSQL JobStep (%'))
BEGIN
 DECLARE @JobID UNIQUEIDENTIFIER;

 SELECT @JobID = CONVERT(UNIQUEIDENTIFIER, 
                          CONVERT(BINARY(16),
                                  SUBSTRING(sdes.[program_name],
                                       CHARINDEX(N'(Job 0x', sdes.[program_name]) + 5,
                                            34), 1
                                 )
                         )
 FROM  sys.dm_exec_sessions sdes
 WHERE sdes.[session_id] = @@SPID;

--SELECT @JobID;

 SELECT @StartedBy = N'sysjobhistory.instance_id: '
                      + CONVERT(NVARCHAR(20), MAX(sjh.[instance_id]))
 FROM   msdb.dbo.sysjobhistory sjh
 WHERE  sjh.[job_id] = @JobID;
END;
ELSE
BEGIN
 SET @StartedBy = ORIGINAL_LOGIN();
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, @StartedBy);

SET @ProcessLogID = SCOPE_IDENTITY();
GO

除了最後一步之外，要呼叫的儲存過程

CREATE PROCEDURE [Logging].[ProcessLog_Log]
(
 @ProcessLogID 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(@RowsInserted, pl.[RowsInserted]),
      pl.[RowsUpdated] = ISNULL(@RowsUpdated, pl.[RowsUpdated]),
      pl.[RowsDeleted] = ISNULL(@RowsDeleted, pl.[RowsDeleted])
FROM   [Logging].[ProcessLog] pl
WHERE  pl.[ProcessLogID] = @ProcessLogID;

IF (@@ROWCOUNT = 0)
BEGIN
RAISERROR('No initial or in-process record for ProcessLogID = %d !', 16, 1,
          @ProcessLogID);
 RETURN;
END;
GO

在最後一步之後和/或在 CATCH 塊中呼叫的儲存過程

CREATE PROCEDURE [Logging].[ProcessLog_Stop]
(
 @ProcessLogID INT,
 @Step TINYINT,
 @StatusID TINYINT,
 @Remarks NVARCHAR(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.[ProcessLogID] = @ProcessLogID;

IF (@@ROWCOUNT = 0)
BEGIN
RAISERROR('No initial or in-process record for ProcessLogID = %d !', 16, 1,
          @ProcessLogID);
 RETURN;
END;
GO

展示儲存過程（輸入參數格式為 XML）

將“StepNumber”放入變數中的原因是可以將值傳遞給CATCH塊。該@StepNumber變數在每次操作之前遞增。如果操作成功，則該值用於呼叫“日誌”儲存過程，該過程擷取該步驟受影響的行數和呼叫時間。如果操作失敗，則使用相同的@StepNumber值來呼叫“停止”儲存過程，該過程將過程標記為“失敗”並傳入錯誤消息。這使得數據不那麼混亂，因為Step失敗記錄的列將是錯誤發生時它實際工作的步驟。

CREATE PROCEDURE [dbo].[ProcessLogDemo]
(
 @Param1 INT,
 @Param2 DATETIME,
 @Param3 NVARCHAR(50) = NULL
)
AS
SET NOCOUNT ON;

DECLARE @ProcessID INT,
       @DB_ID INT = DB_ID(),
       @Params XML,
       @StepNumber TINYINT;

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;

BEGIN TRY

 EXEC [Logging].[ProcessLog_Start]
   @DatabaseID = @DB_ID,
   @ObjectID = @@PROCID,
   @Params = @Params,
   @ProcessLogID = @ProcessID OUTPUT;

 SET @StepNumber = 1;

 -- do something

 EXEC [Logging].[ProcessLog_Log]
   @ProcessLogID = @ProcessID,
   @Step = @StepNumber,
   @RowsSelected = @@ROWCOUNT;

 SET @StepNumber = 2;

 -- do something else

 EXEC [Logging].[ProcessLog_Log]
   @ProcessLogID = @ProcessID,
   @Step = @StepNumber,
   @RowsUpdated = @@ROWCOUNT;

 SET @StepNumber = 3;

 -- do final thingy

 EXEC [Logging].[ProcessLog_Stop]
   @ProcessLogID = @ProcessID,
   @Step = @StepNumber,
   @StatusID = 3, -- success
   @RowsInserted = @@ROWCOUNT;

END TRY
BEGIN CATCH
 DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE();

 EXEC [Logging].[ProcessLog_Stop]
   @ProcessLogID = @ProcessID,
   @Step = @StepNumber,
   @StatusID = 4, -- fail
   @Remarks = @ErrorMessage;
END CATCH;
GO

筆記：

  • 關於獲取 SQL Server 代理作業的“呼叫者”登錄：step_id = 0在作業完成（成功或失敗）之前，記錄（這是該資訊存在的唯一位置）不存在。因此，它在儲存過程執行時不可用，更不用說在開始時了。MAX(sjh.[instance_id]) FROM msdb.dbo.sysjobhistory sjh現在我們為目前會話擷取目前正在執行的作業。稍後（即在作業完成後），可以將其替換為作業呼叫程序登錄。
  • 我通常建議不要將這種類型的日誌記錄添加到非常頻繁執行的儲存過程中，因為額外的讀寫操作會對性能產生負面影響。

附錄

這是一個內聯表值函式 (ITVF)，用於根據instance_id擷取到ProcessLog.StartedBy列中的值獲取作業結果資訊（包括“由”使用者或計劃或其他任何內容）。結果集中返回的instance_id值是step_id = 0.

CREATE FUNCTION dbo.GetSqlServerAgentJobOutcome
(
 @InstanceID INT
)
RETURNS TABLE
AS RETURN

WITH cte AS
(
 SELECT TOP (1)
        sjh.[instance_id],
        sjh.job_id,
        sjh.[message],
        sjh.[run_date],
        sjh.[run_time],
        sjh.[run_duration],
        sjh.[run_status],
        sjh.[sql_message_id],
        sjh.[sql_severity],
        (CHARINDEX(N' was invoked by ', sjh.[message]) + 16) AS [invoker_begin],
        CHARINDEX(N'.  The last step to run', sjh.[message]) AS [invoker_end]
 FROM   msdb.dbo.sysjobhistory  sjh
 WHERE  sjh.[job_id] = (SELECT sjh2.[job_id]
                        FROM   msdb.dbo.sysjobhistory sjh2
                        WHERE  sjh2.[instance_id] = @InstanceID)
 AND    sjh.[step_id] = 0
 AND    sjh.[instance_id] >= @InstanceID
 ORDER BY instance_id ASC
)
SELECT [instance_id], [job_id],
      --[message],
      [run_date], [run_time],
      [run_duration], [run_status],
      [sql_message_id], [sql_severity],
      SUBSTRING([message], invoker_begin, ([invoker_end] - [invoker_begin]))
         AS [InvokedBy]
FROM   cte;
GO

引用自：https://dba.stackexchange.com/questions/151664

相關問答

Sql-Server

如何根據 PartNumber 獲取至少有一個源類型網站和至少一個 stockid 等於 1 的元件號？

  • February 27, 2022
檢視問答
Sql-Server

即使使用非聚集索引和全文索引，SE LECT 查詢也很慢

  • February 21, 2022
檢視問答
Sql-Server

如何在不使用自連接的情況下從表類別中顯示 c 和 x 的類別名稱？

  • February 1, 2022
檢視問答
Sql-Server

在沒有數據庫審計規範的情況下獲取數據庫級審計日誌

  • November 19, 2021
檢視問答
Sql-Server

並行執行儲存過程

  • October 18, 2021
檢視問答
Sql-Server

MS SQL Server 2014：我的錯誤在哪個儲存過程中？

  • April 13, 2021
檢視問答