I set up the SQLWatchImporter as a SQL Agent job on the repository server instance. I only have 2 remote instances configured. Both reside on the same server. One is the default instance name and the other is named STAGE The imported job keeps failing with “Cannot insert duplicate key row in object…” errors on 2 tables from the STAGE instance (sqlwatch_logger_xes_query_processing, sqlwatch_logger_xes_iosubsystem). I’ve tried deleting all the records for that instance from the same repository tables, but the job continues to fail.
Any idea on how to fix this?
Here is the error messages from the log file:
2021-11-08 08:17:58.733 ERROR Failed to merge table “[auslyncdbt01\STAGE].dbo.sqlwatch_logger_xes_query_processing”
at Cannot insert duplicate key row in object ‘dbo.sqlwatch_logger_xes_query_processing’ with unique index ‘idx_sqlwatch_xes_query_processing_event_time’. The duplicate key value is (Nov 7 2021 12:44AM).
;merge dbo.sqlwatch_logger_xes_query_processing as target
using (
select s.* from [#dbo.sqlwatch_logger_xes_query_processing] s
inner join dbo.sqlwatch_logger_snapshot_header h
on s.[snapshot_time] = h.[snapshot_time]
and s.[snapshot_type_id] = h.[snapshot_type_id]
and s.[sql_instance] = h.[sql_instance]) as source
on (source.[snapshot_time] = target.[snapshot_time] and source.[snapshot_type_id] = target.[snapshot_type_id] and source.[sql_instance] = target.[sql_instance] and source.[event_time] = target.[event_time])
when not matched
then insert ([event_time],[max_workers],[workers_created],[idle_workers],[pending_tasks],[unresolvable_deadlocks],[deadlocked_scheduler],[snapshot_time],[snapshot_type_id],[sql_instance])
values (source.[event_time],source.[max_workers],source.[workers_created],source.[idle_workers],source.[pending_tasks],source.[unresolvable_deadlocks],source.[deadlocked_scheduler],source.[snapshot_time],source.[snapshot_type_id],source.[sql_instance]); (Thread: 15)
2021-11-08 08:17:58.827 ERROR Failed to merge table “[auslyncdbt01\STAGE].dbo.sqlwatch_logger_xes_iosubsystem”
at Cannot insert duplicate key row in object ‘dbo.sqlwatch_logger_xes_iosubsystem’ with unique index ‘idx_sqlwatch_xes_iosubsystem_event_time’. The duplicate key value is (Nov 7 2021 12:44AM).
;merge dbo.sqlwatch_logger_xes_iosubsystem as target
using (
select s.* from [#dbo.sqlwatch_logger_xes_iosubsystem] s
inner join dbo.sqlwatch_logger_snapshot_header h
on s.[snapshot_time] = h.[snapshot_time]
and s.[snapshot_type_id] = h.[snapshot_type_id]
and s.[sql_instance] = h.[sql_instance]) as source
on (source.[snapshot_time] = target.[snapshot_time] and source.[snapshot_type_id] = target.[snapshot_type_id] and source.[sql_instance] = target.[sql_instance] and source.[event_time] = target.[event_time])
when not matched
then insert ([event_time],[io_latch_timeouts],[total_long_ios],[longest_pending_request_file],[longest_pending_request_duration],[snapshot_time],[snapshot_type_id],[sql_instance])
values (source.[event_time],source.[io_latch_timeouts],source.[total_long_ios],source.[longest_pending_request_file],source.[longest_pending_request_duration],source.[snapshot_time],source.[snapshot_type_id],source.[sql_instance]); (Thread: 31)