如何修復將 sp_whoisactive 結果記錄到表中的錯誤？

有一個從這裡建模的 sql 代理作業，它使用 sp_whoisactive 將結果擷取到表中。99% 可以正常工作，但 sql 代理作業時不時會失敗並出現以下錯誤：

Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Violation of PRIMARY KEY constraint 'PK__#ADDF8B9__69B13FDC8C10EA7F'. Cannot insert duplicate key in object 'dbo.@blockers'. The duplicate key value is (623). [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153). The step failed.

這是表定義

USE [Maint]
GO

/****** Object:  Table [dbo].[WhoIsActive]    ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[WhoIsActive](
   [dd hh:mm:ss.mss] [varchar](8000) NULL,
   [session_id] [smallint] NOT NULL,
   [sql_text] [xml] NULL,
   [sql_command] [xml] NULL,
   [login_name] [nvarchar](128) NOT NULL,
   [wait_info] [nvarchar](4000) NULL,
   [tran_log_writes] [nvarchar](4000) NULL,
   [CPU] [varchar](30) NULL,
   [tempdb_allocations] [varchar](30) NULL,
   [tempdb_current] [varchar](30) NULL,
   [blocking_session_id] [smallint] NULL,
   [reads] [varchar](30) NULL,
   [writes] [varchar](30) NULL,
   [physical_reads] [varchar](30) NULL,
   [query_plan] [xml] NULL,
   [used_memory] [varchar](30) NULL,
   [status] [varchar](30) NOT NULL,
   [tran_start_time] [datetime] NULL,
   [open_tran_count] [varchar](30) NULL,
   [percent_complete] [varchar](30) NULL,
   [host_name] [nvarchar](128) NULL,
   [database_name] [nvarchar](128) NULL,
   [program_name] [nvarchar](128) NULL,
   [start_time] [datetime] NOT NULL,
   [login_time] [datetime] NULL,
   [request_id] [int] NULL,
   [collection_time] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

是什麼導致了這個錯誤？

sp_whoisactive 版本 = v11.11。

您必須下載更新版本的 Adam Machanic 的sp_whoisactive儲存過程。

最好是 11.16 或更新版本：

版本 11.16 - 2016 年 10 月 18 日（僅限 Box 版本 2005-2017。不適用於 Azure PAAS。）

• 用於辨識“特殊”數據庫頁面（例如 GAM、SGAM）的固定算法。謝謝，羅伯特戴維斯！
• 修復了@blockers 重複鍵錯誤

在程式碼中，@blockers表變數的定義更改為：

...
DECLARE @blockers TABLE
(
session_id INT NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON) /*<<== HERE!*/
);
...

然後，您可以修改現有程式碼。

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