Hi Team,
first of all, thanks for this great product.
I installed SQLWATCH on several servers (SQL Server Standard - different versions) using a central repository. SQLWatchImport running regularly.
After a FullLoad all works fine. But after a while, things go wrong. On some servers the import throws an error on merge to table. Log entry example:
2022-12-16 07:27:44.433 DEBUG Copied 206 rows from “[SERVER01].dbo.sqlwatch_logger_index_missing_stats” to “#dbo.sqlwatch_logger_index_missing_stats” in 40,5646ms. (Thread: 116)
2022-12-16 07:27:44.433 ERROR Failed to merge table “[SERVER01].dbo.sqlwatch_logger_index_missing_stats”
at The MERGE statement conflicted with the FOREIGN KEY constraint “fk_sqlwatch_logger_index_missing_stats_index_detail”. The conflict occurred in database “SQLWATCH”, table “dbo.sqlwatch_meta_index_missing”.set identity_insert dbo.sqlwatch_logger_index_missing_stats on;;merge dbo.sqlwatch_logger_index_missing_stats as target
using (
select s.* from [#dbo.sqlwatch_logger_index_missing_stats] 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.[sql_instance] = target.[sql_instance] and source.[snapshot_time] = target.[snapshot_time] and source.[sqlwatch_database_id] = target.[sqlwatch_database_id] and source.[sqlwatch_table_id] = target.[sqlwatch_table_id] and source.[sqlwatch_missing_index_id] = target.[sqlwatch_missing_index_id] and source.[sqlwatch_missing_index_stats_id] = target.[sqlwatch_missing_index_stats_id] and source.[snapshot_type_id] = target.[snapshot_type_id])
when not matched
then insert ([sqlwatch_database_id],[sqlwatch_table_id],[sqlwatch_missing_index_id],[sqlwatch_missing_index_stats_id],[snapshot_time],[last_user_seek],[unique_compiles],[user_seeks],[user_scans],[avg_total_user_cost],[avg_user_impact],[snapshot_type_id],[sql_instance])
values (source.[sqlwatch_database_id],source.[sqlwatch_table_id],source.[sqlwatch_missing_index_id],source.[sqlwatch_missing_index_stats_id],source.[snapshot_time],source.[last_user_seek],source.[unique_compiles],source.[user_seeks],source.[user_scans],source.[avg_total_user_cost],source.[avg_user_impact],source.[snapshot_type_id],source.[sql_instance]);
set identity_insert dbo.sqlwatch_logger_index_missing_stats off; (Thread: 128)
Some other servers end up with an error on following tables:
2022-12-16 07:27:44.542 ERROR Failed to merge table “[SERVER03].dbo.sqlwatch_logger_disk_utilisation_table”
at The MERGE statement conflicted with the FOREIGN KEY constraint “fk_sqlwatch_logger_disk_utilistation_table_meta_table”. The conflict occurred in database “SQLWATCH”, table “dbo.sqlwatch_meta_table”.
;merge dbo.sqlwatch_logger_disk_utilisation_table as target…
2022-12-16 07:27:44.948 ERROR Failed to merge table “[SERVER04].dbo.sqlwatch_logger_check”
at The MERGE statement conflicted with the FOREIGN KEY constraint “fk_sqlwatch_meta_check”. The conflict occurred in database “SQLWATCH”, table “dbo.sqlwatch_meta_check”.
;merge dbo.sqlwatch_logger_check as target …
2022-12-16 07:27:45.073 ERROR Failed to merge table “[SERVER05].dbo.sqlwatch_logger_perf_procedure_stats”
at The MERGE statement conflicted with the FOREIGN KEY constraint “fk_sqlwatch_logger_perf_procedure_stats_procedure”. The conflict occurred in database “SQLWATCH”, table “dbo.sqlwatch_meta_procedure”.
;merge dbo.sqlwatch_logger_perf_procedure_stats as target…
Thanks for your help !