Symptoms

During a full text index search, all results may not be committed to the SourceOne Search database. Consequently, search results may be incomplete.This issue may not impact every search. The occurrence of this issue will depend on the existence of duplicate messages in the Full Text Index and the order in which those results are returned. This issue is more likely to occur on systems where: Duplicate items exist in the Full Text IndexMaxSearchHitsThreads option has been enabledIn-Place Migration archives exist where data from multiple EmailXtender servers was migrated into a single SourceOne Mapped Folder.

Cause

When running a SourceOne Web Search, the search process will look for matches to the search criteria, or hits, in the index. Once the number of hits reaches 5000, or the search completes, the results are posted to the SourceOne Search SQL database via a SQL transaction. If the transaction contains a duplicate entry, the same message from the same mapped folder, of a hit already posted by the same or previous transaction for the same search, a Primary Key violation error is returned and the transaction is rolled back. Those hits contained in the rolled back transaction will not be added to the result set of the search.

Resolution

Dell EMC recommends that customers apply one of the following SourceOne Email Management patch for the 7.2 version installed: 7.2 SP6 HF4 (7.2.6.6229)7.2 SP7 or higher Downloads are available on the SourceOne Email Management Product page at : http://support.emc.comOnce a version of SourceOne Email Management with the fix is installed all future searches are expected to complete with all results. Any searches performed while running an affected version will need to be executed again to guarantee completeness of results.In SourceOne Email Management version 7.2.8 it may be possible to identify which index sets have duplicate or "Extra Items" content through Index Validation. These validation results can be used to identify which searches which need to be re-run. Perform the following steps: First, configure Index Validation scanning using Mode 2 or higher. Index Validation will flag problem index sets with a yellow exclamation mark (!) and the status of the Index will be "Extra Items". If scans have been run on a previous release a re-scan can be executed in the SourceOne Email Management console by right-clicking each YYYYMM folder and selecting "Rescan". After scans have completed, all indexes with a state of "Extra Items" must be rebuilt successfully before any affected SourceOne Web Searches are re-run.The next steps are to identify any potential SourceOne Web Searches that were previously ran that would have potentially searched across a problem index set.NOTE: The SQL queries below assume that the name of the SourceOne databases are default. If they are not, the query will need to be modified with the correct name of the SourceOne Database Names. First, to identify any past SourceOne Web Searches that were previously ran during the time of this issue, execute the following SQL Queries:1. Run the following query to capture the installation date and upgrade dates of the SourceOne Environment: use ES1Archiveselect EndTime into #TEMP from [DBMaintenanceHistory] where (version >=7 and version insert into #TEMP select EndTime from [DBMaintenanceHistory] where Description = 'New SourceOne Native Archive database created' and Version >=7 and Version GO 2. Run the following query to gather all the information for the SourceOne Web Searches that were ran based on the dates retrieved in Step 1: use ES1Activity;with cte as(select ActivityID as activityid,TaskTypeID,Name as SearchName,search.value('(@searchOperation)[1]','varchar(max)') as operation, search.value('(.)[1]','varchar(max)') as daterange,xConfig.value('(/SearchJobConfig/QueryString/SearchCriteria/@displayName)[1]','nvarchar(max)') as SearchUser from Activitycross apply xConfig.nodes('/SearchJobConfig/QueryString/SearchCriteria/ExpressionSet/SimpleAttributeExpression') as ref(search)where xConfig is not null --and --TaskTypeID = 8and search.value('(@displayName)[1]','varchar(max)') = 'Date')select activityid,TaskTypeID,SearchUser,SearchName, 'StartDate' = Replace(CASE WHEN min(daterange) = max(daterange) THEN 'ALL'ELSE min(daterange) END,'T',' '), 'EndDate' = Replace(CASEWHEN min(daterange) = max(daterange) THEN 'ALL'ELSE max(daterange) END,'T',' ') into #SearchDates from ctegroup by activityid,TaskTypeID,SearchUser,SearchName 3. Run the following query to output all the information regarding the searches that are possibly affected. The output will include the basic information about the SourceOne Web Search including: SearchName:The name of the search as it appears in the SourceOne Email Management Console\Operations\Job Management Window.JobID:The JobID value found in the Job Management windows of the SourceOne Email Management Console.StartTime:The Date\Time that the search was executed.Search Start\End Date:The Date criteria used in the search. If no Date field was used in the search, then ALL dates were searched.Executing user:The user who created and ran the search. use ES1Activityselect distinct A.Name as SearchName,J.JobID,A.StartTime,S.StartDate as [Search Start Date], S.EndDate as [Search End Date], S.SearchUser as [Executing User] from Activity Ajoin Tasks T on A.ActivityID = T.ActivityIDjoin Jobs J on T.TaskID = J.TaskIDjoin #SearchDates S on (S.ActivityID = A.ActivityID)where A.TaskTypeID =8 and A.StartTime > (select min(EndTime) from #TEMP) and A.StartTime order by A.StartTimeGOdrop table #TEMPdrop table #SearchDatesGO NOTE: Only proceed with the following steps after Index Validation has been completed. Next, to identify problematic SourceOne Index sets and their states, execute the following SQL Queries: 1. Run the following query to capture the installation date and upgrade dates of the SourceOne Environment: use ES1Archiveselect EndTime into #TEMP from [DBMaintenanceHistory] where (version >=7 and version insert into #TEMP select EndTime from [DBMaintenanceHistory] where Description = 'New SourceOne Native Archive database created' and Version >=7 and Version GO 2. Execute the following query to capture any SourceOne indexes that were, at any time, marked as "Extra Items": use ES1Archiveselect WorkID,CmdType,cast(CmdParam as XML).value('(/WQ_Parms/WQ_FolderId/node()) [1]', 'bigint') as FolderID,cast(CmdParam as XML).value('(/WQ_Parms/WQ_FldrPath/node()) [1]', 'nvarchar(max)') as [Archive_Index Path],LastModified as ScanDate, SUBSTRING(ResultStr,(CHARINDEX( 'indexflags(', ResultStr, 0)+11),(CHARINDEX( ')', ResultStr, (CHARINDEX( 'indexflags(', ResultStr, 0)+11) )-(CHARINDEX( 'indexflags(', ResultStr, 0)+11))) as ResultStr,'Result' = CAST('Extra Items' as varchar(50))into #ExtraItems from WorkQueue where CmdType =17 and (CHARINDEX( 'indexflags(', ResultStr, 0) > 0 and (CAST(SUBSTRING(ResultStr,(CHARINDEX( 'indexflags(', ResultStr, 0)+11),(CHARINDEX( ')', ResultStr, (CHARINDEX( 'indexflags(', ResultStr, 0)+11) )-(CHARINDEX( 'indexflags(', ResultStr, 0)+11))) as bigint) & CAST( 65536 as bigint) ) > 0)GO 3. Execute the following query to capture any SourceOne Indexes that had been rebuilt during the time of the issue: use ES1Archiveselect WorkID,CmdType,cast(replace(replace(CmdParam,'',''),'','') as nvarchar(max)) as [Archive_Index Path], min(LastModified) as [Rebuild Date], 'Result' = CASE WHEN CmdType=1 THEN 'Rebuilt' ELSE 'Unknown' END into #Rebuilt from WorkQueue (nolock) where CmdType = 1 and (LastModified >= (select min(EndTime) from #TEMP) and LastModified group by WorkID,CmdParam,CmdType order by CmdParam,CmdTypeGOupdate #Rebuilt set [Archive_Index Path] = replace([Archive_Index Path],char(13),'')update #Rebuilt set [Archive_Index Path] = replace([Archive_Index Path],char(10),'')GOset IDENTITY_Insert #ExtraItems ONinsert into #ExtraItems (WorkID,CmdType,[Archive_Index Path],ScanDate,Result)select * from #Rebuilt where NOT EXISTS(select * from #ExtraItems where #ExtraItems.[Archive_Index Path] = #Rebuilt.[Archive_Index Path])GO 4. Execute the following query to capture any SourceOne indexes that were Validation has failed or were missed during the Index Validation process after upgrading to SourceOne 7.2 SP8: use ES1Archiveselect replace(FP.path,'\FPROOT\','') + CASEWHEN LEN(FT.IndexNum) = 1 THEN ('00' + CAST(FT.IndexNum AS VarChar(20)))WHEN LEN(FT.IndexNum) = 2 THEN ('0' + CAST(FT.IndexNum AS varchar(20)))WHEN LEN(FT.IndexNum) = 3 THEN CAST(FT.IndexNum AS varchar(20)) END as [Archive_Index Path] into #IndexList from FTIndex FT join FolderPlan FP on FT.FolderNodeID = FP.FolderIDEXCEPTselect replace(FP.path,'\FPROOT\','') + CASEWHEN LEN(FT.IndexNum) = 1 THEN ('00' + CAST(FT.IndexNum AS VarChar(20)))WHEN LEN(FT.IndexNum) = 2 THEN ('0' + CAST(FT.IndexNum AS varchar(20)))WHEN LEN(FT.IndexNum) = 3 THEN CAST(FT.IndexNum AS varchar(20)) END as [Archive_Index Path] from FTIndex FT join FolderPlan FP on FT.FolderNodeID = FP.FolderIDwhere (FT.LastScanDate > (select Max(Date) from DBMaintenanceHistory where Description = 'SourceOne Archive database update complete' and Version > 7.27))GOset IDENTITY_Insert #ExtraItems ONinsert into #ExtraItems (WorkID,CmdType,[Archive_Index Path],Result) select '0','17',[Archive_Index Path],'Result'='Not-Validated' from #IndexList WHERE NOT EXISTS (select * from #ExtraItems where #ExtraItems.[Archive_Index Path] = #IndexList.[Archive_Index Path])GO 5. Executing this final query will output all the potentially affected SourceOne indexes. Any searches that ran against indexes were marked as "Extra Items" would need to be re-executed to get the correct results. Any searches that were ran against indexes that were "Rebuilt" could potentially need to be re-executed because a rebuild of an index would have resolved but depends on when the search was ran against the rebuilt index set. select WorkID,[Archive_Index Path],ScanDate as Date,Result from #ExtraItems where Result !='Validated'GOdrop table #TEMPdrop table #Rebuiltdrop table #ExtraItemsdrop table #IndexListGO

Support Cases