How to see the number of items waiting to be indexed for each index volume

Description

This information can only be obtained from the SQL server.

Note: The SQL queries below assume that the Vault Store database and EnterpriseVaultDirectory database are located on the same SQL server. An alternative approach would be needed if this was not the case.

1.    Run the following SQL query against the Vault Store database to determine the number of new items waiting to be indexed:

SELECT Records, ArchiveName
FROM
(SELECT ArchivePoint.ArchivePointId, count(*)  Records
FROM JournalArchive
INNER JOIN ArchivePoint on ArchivePoint.ArchivePointIdentity = JournalArchive.ArchivePointIdentity
WHERE IndexCommited=0
GROUP by JournalArchive.ArchivePointIdentity, ArchivePoint.ArchivePointId) SQ
INNER JOIN EnterpriseVaultDirectory.dbo.ArchiveView ON ArchiveView.VaultEntryId = SQ.ArchivePointId
ORDER by Records desc

This provides a list of the index volumes that have items waiting to be indexed. The list is ordered by the number of items waiting to be indexed.

2.    Run the following SQL query against the Vault Store database to determine the number of indexed items waiting to be updated:

SELECT Records, ArchiveName
FROM
(SELECT ArchivePoint.ArchivePointId, count(*)  Records
FROM JournalUpdate
INNER JOIN ArchivePoint on ArchivePoint.ArchivePointIdentity = JournalUpdate.ArchivePointIdentity
WHERE IndexCommitted=0
GROUP by JournalUpdate.ArchivePointIdentity, ArchivePoint.ArchivePointId) SQ
INNER JOIN EnterpriseVaultDirectory.dbo.ArchiveView ON ArchiveView.VaultEntryId = SQ.ArchivePointId
ORDER by Records desc

This provides a list of the index volumes that have items waiting to be updated. The list is ordered by the number of items waiting to be updated.

3.    Run the following SQL query against the Vault Store database to determine the number of indexed items waiting to be deleted:

SELECT Records, ArchiveName
FROM
(SELECT ArchivePoint.ArchivePointId, count(*)  Records
FROM JournalDelete
INNER JOIN ArchivePoint on ArchivePoint.ArchivePointIdentity = JournalDelete.ArchivePointIdentity
WHERE IndexCommitted=0
GROUP by JournalDelete.ArchivePointIdentity, ArchivePoint.ArchivePointId) SQ
INNER JOIN EnterpriseVaultDirectory.dbo.ArchiveView ON ArchiveView.VaultEntryId = SQ.ArchivePointId
ORDER by Records desc

These SQL queries provide a list of the index volumes that have items waiting to be deleted from the index. The list is ordered by the index volumes with the most number of items waiting to be deleted at the top.