cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Query for Index bit-type?

Elio_C
Level 6

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"
1 ACCEPTED SOLUTION

Accepted Solutions

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

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

 

Regards. Gertjan

View solution in original post

5 REPLIES 5

VirgilDobos
Moderator
Moderator
Partner    VIP    Accredited Certified

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.

--Virgil

Elio_C
Level 6

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

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

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

 

Regards. Gertjan

GabeV
Level 6
Employee Accredited

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.

Elio_C
Level 6

Excellent Gertjan, many thanks