3

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?

4

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
4

It appears from the Primary Key name that you are running Adam Machanic's sp_WhoIsActive and storing the result in a table.

If that is the case, the warnings are normal and caused by the options you set when calling sp_WhoIsActive. The Primary Key violation is what is causing the job to fail.

Take a look at the definition of the primary key.

I believe you will find that the Primay Key needs to be changed in order to insure uniqueness.

In my implementations for this I leave the table as a heap and do not define a primary key on this table. I only keep a few days of data in the table and do not use it for replication.

Here is an article on how to implement it: Brent Ozar Implementation of sp_WhoIsActive

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.