pr_sys_Workindexer_queue table is full
pr_Sys_WorkIndexer_queue tabel is full with data. How can we reduce it?
Pegasystems
IN
- Which version of PRPC is this?
- On the search landing page (Pega Icon -> System -> Settings -> Search), please check that the configuration is correct and the "SystemWorkIndexer” agent is running. This agent should clear up the queue.
You might want to refer to this PDN article on how to fix the search landing page - https://pdn.pega.com/deployment/configuring-full-text-search-for-high-availability
Pegasystems
IN
Sorry, but is the agent not working (exceptions) or are the queue entries not decreasing?
Did you check if you had the right node id mentioned on the search landing page in the index host node settings?
Pegasystems
IN
Note that if you do truncate the table, the search index is out of sync with the changes in the database. You will have to re-index "Data" and "Work" from the search landing page.
Cognizant
IN
So if we truncate the table there will be no problem right?
Once truncate is done successfully we will do the reindexing of Data and Work from search landing page.
Pegasystems Inc.
GB
I would first check the individual counts for each type of status the items in the Queue Table are; start with something like this:
select distinct(PYITEMSTATUS),count(*) from <schema>.pr_sys_workindexer_queue
group by pyitemstatus order by pyitemstatus
It might take a while to complete.
One possibility is that you have a lot 'Broken-Process' items in there.
If you have; maybe consider the following plan (don't just go ahead and do this though ! if you are unsure - log an SR with GCS and we can work with you!)
- Stop PRPC. (Better not to have new stuff being placed in the table).
- Using Native DB tools: Create a backup table of any 'broken items'; something like this: (As noted in the comment in the SQL: double-check my spelling of 'Broken-Process' here)
CREATE TABLE <schema>.backup_pr_sys_workindexer_queue
as SELECT * from <schema>.pr_sys_workindexer_queue
where pyitemstatus='Broken-Process'; // check this string - I'm typing from memory here !
- Once you have a 'safe' copy of the broken stuff - COMMIT them to DB ; and then you can delete the originals.
delete from <schema>.pr_sys_workindexer_queue
where pyitemstatus='Broken-Process';
Do another COMMIT here.
I would first check the individual counts for each type of status the items in the Queue Table are; start with something like this:
select distinct(PYITEMSTATUS),count(*) from <schema>.pr_sys_workindexer_queue
group by pyitemstatus order by pyitemstatus
It might take a while to complete.
One possibility is that you have a lot 'Broken-Process' items in there.
If you have; maybe consider the following plan (don't just go ahead and do this though ! if you are unsure - log an SR with GCS and we can work with you!)
- Stop PRPC. (Better not to have new stuff being placed in the table).
- Using Native DB tools: Create a backup table of any 'broken items'; something like this: (As noted in the comment in the SQL: double-check my spelling of 'Broken-Process' here)
CREATE TABLE <schema>.backup_pr_sys_workindexer_queue
as SELECT * from <schema>.pr_sys_workindexer_queue
where pyitemstatus='Broken-Process'; // check this string - I'm typing from memory here !
- Once you have a 'safe' copy of the broken stuff - COMMIT them to DB ; and then you can delete the originals.
delete from <schema>.pr_sys_workindexer_queue
where pyitemstatus='Broken-Process';
Do another COMMIT here.
Now re-run the original SQL to count the different types of items in the queue; hopefully you should now have significantly less that 30 Million (wow! by the way!) entries you currently have.
You can usually do this sort of thing from SMA / prsysmgmt - but unfortunately the UI will break if there is a really high number of entries like you have here. (As the UI tries to list all the existing entries for you - this doesn't scale past a few hundred items really).