Forum Discussion

Elio_C's avatar
Elio_C
Level 6
11 years ago
Solved

SQL Query for Index bit-type?

Hi,

I'm looking for a way to identify the 32-bit indexes from SQL.

The purpose is to identify 'older' archives and update them to 64-bit indexes outside of the active user updgrade schedule.

I have the below to identify 'older' archives but can't seem to find an obvious index bit-type in any of the tables.

Any help greatly appreciated.

Thanks

SELECT  A.ArchiveName "Archive Name",
        AP.ModifiedDate "Archive Last Modified",
        COUNT(s.ArchivePointIdentity) "Item Count"
FROM    EnterpriseVaultDirectory.dbo.Archive A,
        EnterpriseVaultDirectory.dbo.Root R,
        EVVSEVARCAPSTR01_1.dbo.ArchivePoint AP,
        EVVSEVARCAPSTR01_1.dbo.Saveset S
WHERE   A.RootIdentity = R.RootIdentity
  AND   R.VaultEntryId = AP.ArchivePointId
  AND   AP.ArchivePointIdentity = S.ArchivePointIdentity
  AND    AP.ModifiedDate < '2014-01-01'
GROUP BY A.ArchiveName, AP.ModifiedDate
ORDER BY "Archive Name"
  • Hiya,

    I used the below. I added "and AR.ArchiveName like 's%', to be able to get lesser results. You could remove that. query orders names per items to index.

    USE EnterpriseVaultDirectory
    Select AR.ArchiveName, IV.RootIdentity, IV.IndexedItems, IV.FolderName, IV.IndexVolumeType from IndexVolume IV
    Join Archive AR on IV.RootIdentity = AR.RootIdentity
    Where IV.IndexVolumeType = 0
    and AR.ArchiveName like 'S%'
    order by iv.IndexedItems

     

  • Hi there, you can see that within the EV console too if you go Index Management dashboard. Then, you can add index volumes to be upgraded and set a schedule for the Index Administration task based on the time you want it to run.

    Hope this helps.

  • Thanks Virgil,

    The specific issue is that I have many thousands of archives, active and inactive, to upgrade and my schedule is indiscriminate.

    What I'd like to do is target the active users during my schedule to get them all upgraded (project complete!) and then target the older, inactive archives later. The SQL query gives me an archive last modified date and I can identify the inactive archives.

    I guess anyway to export the archives with 32-bit indexes would be OK. I have been getting the folder names from the closed 32-bit indexes and matching the archive IDs but this is becoming very tedious when scaled to '000s

  • Hiya,

    I used the below. I added "and AR.ArchiveName like 's%', to be able to get lesser results. You could remove that. query orders names per items to index.

    USE EnterpriseVaultDirectory
    Select AR.ArchiveName, IV.RootIdentity, IV.IndexedItems, IV.FolderName, IV.IndexVolumeType from IndexVolume IV
    Join Archive AR on IV.RootIdentity = AR.RootIdentity
    Where IV.IndexVolumeType = 0
    and AR.ArchiveName like 'S%'
    order by iv.IndexedItems

     

  • Hello,

    Also, to simplify the query a little bit, in the Enterprise Vault directory database you can use the dbo.IndexVolumeView view and filter the results using IndexVolumeType and ArchiveName as GertjanA mentioned on his query.

    I hope this helps.