How to find the specific location of an uncollected DVS file in EV8+

Problem

Often times there are errors in the Enterprise Vault event logs that reference a specific Saveset or Transaction ID.  This information can be used to identify if the physical files exist on disk in the vault store as part of the troubleshooting process.  

Solution

The following SQL query can be used to indentify the location of the Saveset based on the information contained in the Vault Store DB.

USE [Vault Store DB here]
select PartitionRootPath+'\'+ cast(datepart(year,ArchivedDate)as char(4))+'\'+
right(cast(datepart(month,ArchivedDate)+100 as char(3)),2)+'-'+
right(cast(datepart(day,ArchivedDate)+100 as char(3)),2)+'\'+
LEFT(IdTransaction,1)+'\'+
left(right(IdTransaction,len(IdTransaction)-1),3) AS Path, IdTransaction AS 'DVS Filename' from  Saveset
left join Vault on saveset.VaultIdentity=Vault.VaultIdentity
inner join EnterpriseVaultDirectory.dbo.VaultEntryView on Vault.VaultID = VaultEntryView.VaultEntryId
inner join EnterpriseVaultDirectory.dbo.PartitionEntry on Saveset.IdPartition = PartitionEntry.IdPartition and PartitionEntry.VaultStoreEntryId = VaultEntryView.VaultStoreEntryId
Where idtransaction = '[TransactionID]'

Note:  Where [VaultStoreDB] is the name of the Vault Store database and [TransactionID] is the Transaction ID or Saveset ID noted in the Event ID. E.g.  '4094EF39-FA35-1DA0-7C01-5F01616C3D91'

The output of this query will result in two columns:  Path & DVSfilename

With this output, it can now be confirmed if the file exists or not.