Have a sql agent job modeled from here that uses sp_whoisactive to capture results to a table. 99% it works fine but every now and then the sql agent job will fail with this error:
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.
Here is table def
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
What is causing this error?
sp_whoisactive version = v11.11.
