cancel
Showing results for 
Search instead for 
Did you mean: 

identify index failed items in archives

AndrewB
Moderator
Moderator
Partner    VIP    Accredited
calling all EV SQL gurus... i'm looking for a query to find the names of all the archives that have index failed items. part 2 which would be awesome but it's not critical would be a way to repair them without having to invidiually go to the properties of each archive and run a repair from the 'index volumes' tab.
the indexes themselves arent failed so they dont show up in the "manage index volumes" tool.
1 ACCEPTED SOLUTION

Accepted Solutions

Liam_Finn1
Level 6
Employee Accredited Certified
Here is the one that we use

SELECT ArchiveName AS 'Archive', FolderName AS 'Folder', [Offline] AS 'Index Offline', Failed AS 'Index Failed', Rebuilding AS 'Index Rebuilding',
failedItems AS 'Failed Items'
FROM Archive a, IndexVolume iv 
WHERE a.RootIdentity = iv.RootIdentity and
([Offline] = 'True' or Failed = 'True' or Rebuilding = 'True' or failedItems <> '')

We have it as a scheduled job to be run on SQL reporting services so it runs 3 times a day

View solution in original post

5 REPLIES 5

Nick_White
Level 6
Employee
In the EV Directory database look in the IndexVolumes table, which includes a FailedItems count column. The rootIdentity field maps to the Archive table so that you can get the name of the Archive that has a volume with failed items. Unfortunately there isn't an "update/repair all" option in the product at the moment, so hopefully you won't have many volumes with failed items

Nick_White
Level 6
Employee
Just playing a bit more...! Try this query against the EnterpriseVaultDirectory database

Select ArchiveName from Archive where rootidentity in (select rootidentity from IndexVolume where faileditems > 0)

Liam_Finn1
Level 6
Employee Accredited Certified
Here is the one that we use

SELECT ArchiveName AS 'Archive', FolderName AS 'Folder', [Offline] AS 'Index Offline', Failed AS 'Index Failed', Rebuilding AS 'Index Rebuilding',
failedItems AS 'Failed Items'
FROM Archive a, IndexVolume iv 
WHERE a.RootIdentity = iv.RootIdentity and
([Offline] = 'True' or Failed = 'True' or Rebuilding = 'True' or failedItems <> '')

We have it as a scheduled job to be run on SQL reporting services so it runs 3 times a day

AndrewB
Moderator
Moderator
Partner    VIP    Accredited
Awesome! just what i was looking for!

Liam_Finn1
Level 6
Employee Accredited Certified
Happy to assist.

We have automated a few tasks in this manner to keep a watchful eye over our environment. We have found them to be very useful