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
