主页 / dba / 问题 / 151664
Accepted
mouliin
Asked: 2016-10-08 00:36:34 +0800 CST

存储过程处理和错误日志

  • 772

我曾经在一家拥有第三方数据仓库解决方案的公司工作。显然，所有对象和表都隐藏在支持数据库中，所以我不清楚某些存储过程中到底发生了什么。我在那里看到了这个有趣的存储过程，并想在我自己的解决方案中复制它，但我无法理解它是如何工作的。我正在描述下面的存储过程，如果有人能给我一些关于如何实现这一点的想法，那将非常有帮助。如果你能建议我如何让它变得更好，那就更好了。

存储过程称为进程日志。它具有 DBID、ObjectId、Step、Status、Remarks、Reads、Inserts、Updates、Delete 等参数

我们要做的是，在每个存储过程中，我们必须执行这个状态为 2 (In Progress) 的存储过程。的可变步长。基于 Insert update select 和 delete 语句的行数，我们应该在各自的存储过程参数变量中记录值。最后，您可以执行状态为 3（已完成）的相同存储过程，或者如果该过程以 catch 块结束，则在备注部分中状态将为 4（失败），我们可以复制 SQL 的错误消息。

要查看所有这些信息，我们可以访问报告，显然我没有源代码，但报告显示了存储过程在完成时开始的时间，状态是多少插入更新删除并读取它做过。如果失败，错误信息是什么？

我已经没有什么改进商店的想法了，谁开始的？参数的值是多少？对于谁开始存储过程部分，我很困惑。大多数这些存储过程作为不同作业的一部分运行。我们所有的作业都作为服务帐户用户运行，但作业是由不同的用户手动启动的。我需要找出哪个用户启动了它，就像在存储过程中一样，作为当前用户，它总是会显示服务帐户。同样对于参数值，是否有更好的动态方法来找出这一点？而不是手动设置变量的值。我想使用 INPUTBUFFER 的输出，但它只显示参数的名称而不是值。

如果有人可以指导我有关此审计 SP 的后端表结构和脚本，那将非常有帮助。也欢迎任何更多的改进想法。

我的主要困惑：我相信他们有一些存储这些存储过程值的表，如果 SP 已经在运行，他们确实在记录中更新然后执行插入，但他们如何确定在场景中执行插入而不是更新其中存储过程严重失败并且未执行 catch 块。

stored-procedures sql-server-2014

1 个回答

  1. Best Answer

    这是一个至少非常接近的结构。

    没有编程方式来获取参数（不幸的是）。您需要将它们格式化为 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
    • 5

相关问题