Sql-server – Automated SP_WhoIsActive SQL Activity Capturing Issue

jobssp-whoisactivesql serversql-server-2008-r2sql-server-agent

I have an SQL Agent Job on a production server that keeps failing with the below messages. It is supposed to be capturing SQL Server activity using the SP_WHOISACTIVE stored proc, at regularly scheduled intervals.

Executed as user: 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)

Warning: Null value is eliminated by an aggregate or other SET
operation. [SQLSTATE 01003] (Message 8153)

Violation of PRIMARY KEY constraint 'PK_WhoIsActive'. Cannot insert
duplicate key in object 'monitoring.WhoIsActive'. The duplicate key
value is (Jan 20 2017 8:25AM, 109). [SQLSTATE 23000] (Error 2627)

The statement has been terminated. [SQLSTATE 01000] (Error 3621).

The step failed.

Any idea what may be causing this?

What steps should I follow to fix this error?

Best Answer

Automate capturing SQL Server activity with SP_WHOISACTIVE and SP_WHO2 via a SQL Agent job

Any idea what may be causing this?

Obviously your current process has an issue trying to insert duplicate data on a table with a constraint that doesn't allow such data to be duplicated where the value is Jan 20 2017 8:25AM, 109 per the defined PK_WhoIsActive Primary Key.

What steps should I follow to fix this error?

I've copied the logic I've used with success on this sort of task below. I run the create table logic once (for both) but save it to a commented out or skipped SQL Agent step for future reference only.

I shared both the SP_WHOISACTIVE and the SP_WHO2 capturing methods and logic below but be sure you're on the DB to run the SP_WHO2 processes as it's logic is implicit. Be sure to specify the table and DB Names in the SP_WHOISACTIVE processes since its logic is explicit.

SP_WhoIsActive Table Capturing

Create Table

DECLARE @destination_table VARCHAR(4000);

SET @destination_table = 'WhoIsActive_Cap';

DECLARE @schema VARCHAR(4000);

EXEC sp_WhoIsActive @get_transaction_info = 1
    ,@get_plans = 1
    ,@return_schema = 1
    ,@schema = @schema OUTPUT;

SET @schema = REPLACE(@schema, '<table_name>', @destination_table);

PRINT @schema

EXEC (@schema);

Capture to Table

SET @destination_table = '[<DB_Name>].dbo.WhoIsActive_Cap'

EXEC Master.dbo.sp_WhoIsActive @get_transaction_info = 1
    ,@get_plans = 1
    ,@destination_table = @destination_table;

-- remove records older than 7 days but adjust accordingly for your needs
DELETE
FROM [<DB_Name>].dbo.WhoIsActive_Cap
WHERE collection_time < dateadd(dd, -7, getdate())

SP_WHO2 Table Capturing

Create Table

CREATE TABLE dbo.who2cap (
    SPID INT
    ,[Status] VARCHAR(100)
    ,[Login] VARCHAR(100)
    ,HostName VARCHAR(50)
    ,BlkBy VARCHAR(10)
    ,DBName VARCHAR(255)
    ,Command VARCHAR(500)
    ,CPUTime INT
    ,DiskIO INT
    ,LastBatch VARCHAR(20)
    ,ProgramName VARCHAR(500)
    ,SPID2 INT
    ,REQUESTID INT
    )

Capture to Table

CREATE TABLE #who2cap (
    SPID INT
    ,[Status] VARCHAR(100)
    ,[Login] VARCHAR(100)
    ,HostName VARCHAR(50)
    ,BlkBy VARCHAR(10)
    ,DBName VARCHAR(255)
    ,Command VARCHAR(500)
    ,CPUTime INT
    ,DiskIO INT
    ,LastBatch VARCHAR(20)
    ,ProgramName VARCHAR(500)
    ,SPID2 INT
    ,REQUESTID INT
    )

INSERT #who2cap
EXEC sp_who2

-- remove old entries (currently set to 3 weeks)
DELETE
FROM dbo.who2cap
WHERE logdt < dateadd(dd, - 21, getdate())

INSERT INTO dbo.who2cap
SELECT getdate()
    ,*
FROM #who2cap

DROP TABLE #who2cap
Related Question