Venio Console: Remove the Indexing Job

The Problem

The media is large. Auto indexing was on during data ingestion. We want to stop it so that the ingestion finishes faster.

Resolution

1. Execute the query below in your project database. This query below will pause the indexing job.

UPDATE tbl_ds_ProjectJobInfo WITH (ROWLOCK)
SET OtherSettings.modify('insert
<IsJobPaused>1</IsJobPaused>
as last into (/OtherSettings)[1]')
WHERE Name='Indexing' AND Status NOT IN (2, -2) AND
OtherSettings.exist('/OtherSettings/IsJobPaused') = 0

UPDATE tbl_ds_ProjectJobInfo WITH (ROWLOCK)
SET OtherSettings.modify('replace value of
(OtherSettings/IsJobPaused/text ())[1] with "1"')
WHERE Name='Indexing' AND Status NOT IN (2,-2) AND 
OtherSettings.value('(/OtherSettings/IsJobPaused)[1]','BIT') = 0

2. After the ingestion job is completed, execute the query below in project database. The query below will remove not started file queued for indexing job and resume indexing job. This will pick indexing job and eventually complete the job.

DELETE FROM tbl_ex_IndexStatusLog WHERE [status] < 1
UPDATE tbl_ds_ProjectJobInfo WITH (ROWLOCK)
Set OtherSettings.modify('replace value of (OtherSettings/IsJobPaused/text())[1] with "0"')
WHERE Name='Indexing' AND Status NOT IN (2,-2) 
AND OtherSettings.value('(OtherSettings/IsJobPaused)[1]','bit')=1

3. After completion on indexing job execute query below query in project database. This will mark all media as indexed and also set disable index in project level.

Update tbl_pj_ProjectSetup WITH(ROWLOCK) set
 IndexingFlag=0, IndexEmailHeader=0, 
IndexFullText=0, IndexMeta=0 WHERE DatabaseInstanceName=DB_NAME()
update tbl_ex_Media WITH(ROWLOCK) SET indexed=2