The ItemNSSource table seems excessively large

Products

• IT Management Suite

Issue/Introduction

Currently there is no process that cleans up the ItemNSSource table so as Items are deleted it leaves orphaned rows in this table.  

We have not noted any major concerns with this however as tables get larger it can cause performance problems. 

 NA

Cause

 Replication can cause bloat here as there are many items created solely for the Replication process that are deleted as replication complete but are left in the ItemNSSource table.

Resolution

ITMS 7.1 / 7.5 / 7.6 

 Run the following SQL to see how many rows are currently orphaned.  If the count is greater than 10 million it is worth running the secondary script to delete the orphaned data.

declare @totalcount as int
declare @goodcount as int 

select @totalcount = count (*)
from ItemNSSource

select @goodcount = count (ins.ItemGuid) 
from ItemNSSource ins
left join NSInternal_ItemInstalled nii on nii.Guid = ins.ItemGuid
left join Item i on i.Guid = ins.ItemGuid
left join vRM_Resource r on r.Guid = ins.ItemGuid
where i.Guid is not null or nii.Guid is not null or r.Guid is not null

select @totalcount - @goodcount as OrphanedCount

Query to delete orphaned entries

if exists (select name from sys.sysobjects where name = 'TempItemNSSource')
begin drop table TempItemNSSource end
create Table TempItemNSSource (ItemGuid uniqueidentifier, OriginNSSourceNSId int)
declare @count as int

insert into TempItemNSSource
select ins.* 
from ItemNSSource ins
left join NSInternal_ItemInstalled nii on nii.Guid = ins.ItemGuid
left join Item i on i.Guid = ins.ItemGuid
left join vRM_Resource r on r.Guid = ins.ItemGuid
where i.Guid is not null or nii.Guid is not null or r.Guid is not null

select @count = count (ins.ItemGuid) 
from ItemNSSource ins
left join NSInternal_ItemInstalled nii on nii.Guid = ins.ItemGuid
left join Item i on i.Guid = ins.ItemGuid
left join vRM_Resource r on r.Guid = ins.ItemGuid
where i.Guid is not null or nii.Guid is not null or r.Guid is not null

if (select count (t.ItemGuid) from TempItemNSSource t) = @count
    begin
        truncate table ItemNSSource
    end

insert into ItemNSSource
select distinct tins.* from TempItemNSSource tins
left join ItemNSSource ins on ins.ItemGuid = tins.ItemGuid
where ins.ItemGuid is null

 

ITMS 8.0

Install ITMS 8.0 HF6.