OPERATIONAL DEFECT DATABASE
...
...
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 Index MaxSearchHitsThreads option has been enabled In-Place Migration archives exist where data from multiple EmailXtender servers was migrated into a single SourceOne Mapped Folder.
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.
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.com Once 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 ES1Archive select EndTime into #TEMP from [DBMaintenanceHistory] where (version >=7 and version <7.27000) and Description = 'SourceOne Archive database update complete' insert into #TEMP select EndTime from [DBMaintenanceHistory] where Description = 'New SourceOne Native Archive database created' and Version >=7 and Version < 7.27000 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 Activity cross apply xConfig.nodes('/SearchJobConfig/QueryString/SearchCriteria/ExpressionSet/SimpleAttributeExpression') as ref(search) where xConfig is not null --and --TaskTypeID = 8 and 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(CASE WHEN min(daterange) = max(daterange) THEN 'ALL' ELSE max(daterange) END,'T',' ') into #SearchDates from cte group 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 ES1Activity select 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 A join Tasks T on A.ActivityID = T.ActivityID join Jobs J on T.TaskID = J.TaskID join #SearchDates S on (S.ActivityID = A.ActivityID) where A.TaskTypeID =8 and A.StartTime > (select min(EndTime) from #TEMP) and A.StartTime < (select max(EndTime)from #TEMP) order by A.StartTime GO drop table #TEMP drop table #SearchDates GO 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 ES1Archive select EndTime into #TEMP from [DBMaintenanceHistory] where (version >=7 and version <7.27000) and Description = 'SourceOne Archive database update complete' insert into #TEMP select EndTime from [DBMaintenanceHistory] where Description = 'New SourceOne Native Archive database created' and Version >=7 and Version < 7.27000 GO 2. Execute the following query to capture any SourceOne indexes that were, at any time, marked as "Extra Items": use ES1Archive select 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 ES1Archive select WorkID,CmdType,cast(replace(replace(CmdParam,'<WQ_Parms><WQ_FldrPath>',''),'</WQ_FldrPath></WQ_Parms>','') 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 < (select max(EndTime) from #TEMP)) group by WorkID,CmdParam,CmdType order by CmdParam,CmdType GO update #Rebuilt set [Archive_Index Path] = replace([Archive_Index Path],char(13),'') update #Rebuilt set [Archive_Index Path] = replace([Archive_Index Path],char(10),'') GO set IDENTITY_Insert #ExtraItems ON insert 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 ES1Archive select replace(FP.path,'\FPROOT\','') + CASE WHEN 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.FolderID EXCEPT select replace(FP.path,'\FPROOT\','') + CASE WHEN 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.FolderID where (FT.LastScanDate > (select Max(Date) from DBMaintenanceHistory where Description = 'SourceOne Archive database update complete' and Version > 7.27)) GO set IDENTITY_Insert #ExtraItems ON insert 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' GO drop table #TEMP drop table #Rebuilt drop table #ExtraItems drop table #IndexList GO
Click on a version to see all relevant bugs
Dell Integration
Learn more about where this data comes from
Bug Scrub Advisor
Streamline upgrades with automated vendor bug scrubs
BugZero Enterprise
Wish you caught this bug sooner? Get proactive today.
