Hi,
I’m new to this forum and also new to SQLWATCH and loving the product.
However, I’d like to raise an issue we are experiencing and not sure if this forum is the right place. Apologies if not.
Step 1 of job SQLWATCH-LOGGER-INDEXES continually fails.
Running EXEC [dbo].[usp_sqlwatch_internal_add_index] results in:-
Msg 8672, Level 16, State 1, Procedure usp_sqlwatch_internal_add_index, Line 91
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
Doing a bit of digging around and in this proc, temp table ##DB61B2CD92324E4B89019FFA7BEF1010 has duplicates when grouping on sqlwatch_database_id, sqlwatch_table_id and index_name (there’s only one instance) and it’s these which are the columns used in the MERGE join and is the source of the failure.
We have a certain database used for archiving and the tables have no primary key resulting in indexes with no name in sys.indexes and of type HEAP. However, some tables have a non-clustered index whose name the same name as the table. Looking at the code below which is extracted from the proc -
insert into ##DB61B2CD92324E4B89019FFA7BEF1010 (index_name, index_id, index_type_desc, [table_name], [database_name])
exec [dbo].[usp_sqlwatch_internal_foreachdb] @databases = @databases, @command = ‘use [?]
insert into ##DB61B2CD92324E4B89019FFA7BEF1010 (index_name, index_id, index_type_desc, [table_name], [database_name])
select isnull(ix.name,object_name(ix.object_id)), ix.index_id, ix.type_desc, s.name + ‘’.’’ + t.name, ‘’?’’
from sys.indexes ix
inner join sys.tables t
on t.[object_id] = ix.[object_id]
inner join sys.schemas s
on s.[schema_id] = t.[schema_id]
where objectproperty( ix.object_id, ‘‘IsMSShipped’’ ) = 0 ', @calling_proc_id = @@PROCID
index_name is populated by isnull(ix.name,object_name(ix.object_id)). The HEAP indexes have a NULL in ix.name and so will be given the default name of the table by object_name(ix.object_id) - hence we have two rows output with the same database_name, table_name and index_name, although index_Id’s will be different values (0 and 15).
Does anyone else experience this?
Would a fix in the proc MERGE statement be sufficient so we JOIN using index_id as well?
So,
merge [dbo].[sqlwatch_meta_index] as target
using ##DB61B2CD92324E4B89019FFA7BEF1010 as source
on target.sqlwatch_database_id = source.sqlwatch_database_id
and target.sqlwatch_table_id = source.sqlwatch_table_id
and target.sql_instance = @@SERVERNAME
and target.index_name = source.index_name collate database_default
becomes
merge [dbo].[sqlwatch_meta_index] as target
using ##DB61B2CD92324E4B89019FFA7BEF1010 as source
on target.sqlwatch_database_id = source.sqlwatch_database_id
and target.sqlwatch_table_id = source.sqlwatch_table_id
and target.sql_instance = @@SERVERNAME
and target.index_name = source.index_name collate database_default
and target.index_id = source.index_id
Thanks,