Hey all, I figured I throw these out to everyone. These are a few queries I use to help identify issues with backlogs for items awaiting for backup (backupcomplete) and committed to the index (IndexCommited). These give a good healthcheck on the Vault Stores, your indexing and your backups since if these table becomes too large (2M+ records) this can cause other issues in the environment, especially performance.
As with all SQL Queries, this is presented as is and so must be run with caution.
This query does a simple check on the JournalArchive table of the Vault Store DB to determine counts, based on the IdPartition, BackupComplete and IndexCommited attributes.
If BackupComplete = 0, these items have not been secured via backup.
If IndexCommited = 0, these items have not been added to the associated index.
For 11.0 and above, if Idpartition = '-1', these items are in the Storage Queues and have not been placed on storage yet.
SELECT COUNT(IdPartition) AS ItemCount,
from JournalArchive) JA
IdPartition, BackupComplete, IndexCommited
This query relates the JournalArchive and Watchfile tables of the VaultStore DB. Much like the above query, this gives counts on the same attributes, but also shows the related counts in the Watchfile table(especially helpful when troubleshooting backlogs due to items awaiting backup). It also adds the ArchivePointId (ArchiveVEID value for individual Archives). This part also then helps for indexing issues in case one particular Archive has a problem updating it's indexes.
Note: This is a bit more impactful performance-wise so execute with caution.
SELECT Count(JA.TransactionID) AS "JACount",
Count (WF.ArchiveTransactionID)AS "WFCount",
(SELECT TransactionID, ArchivePointIdentity,BackupComplete,IndexCommited,IdPartition
FROM JournalArchive) JA
(SELECT ArchivePointId,ArchivePointIdentity FROM ArchivePoint) AP
ON JA.ArchivePointIdentity = AP.ArchivePointIdentity
LEFT OUTER JOIN
(Select ArchiveTransactionId From Watchfile) WF
ON JA.TransactionID = WF.ArchiveTransactionID
-- Remove the below "--" to uncomment the WHERE clause to limit this query for single archives.
-- WHERE JA.ArchivePointId = ''
GROUP BY AP.ArchivePointID, JA.ArchivePointIdentity, JA.idPartition, JA.BackupComplete, JA.IndexCommited
ORDER BY JA.ArchivePointIdentity
I hope these help!