Useful SQL Queries - JournalArchive and Watchfile counts

[Chris_Warren]

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.

Basic check:

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,
            BackupComplete,
            IndexCommited,
            IdPartition
    FROM       
    (Select TransactionID,IdPartition,BackupComplete,IndexCommited 
            from JournalArchive) JA
    GROUP BY   
    IdPartition, BackupComplete, IndexCommited

Detailed check:

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.

--USE EVVSVaultStoreName_1
    SELECT Count(JA.TransactionID) AS "JACount",
        Count (WF.ArchiveTransactionID)AS "WFCount",
        JA.ArchivePointIdentity,
        JA.BackupComplete,
        JA.IndexCommited,
        JA.IdPartition,
        AP.ArchivePointId
    FROM 
    (SELECT TransactionID, ArchivePointIdentity,BackupComplete,IndexCommited,IdPartition 
       FROM JournalArchive) JA 
    INNER JOIN
    (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!

Chris