Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

SQL query to show dates of archived items in index volumes

Created: 21 Sep 2012 • Updated: 27 Sep 2012 | 10 comments
This issue has been solved. See solution.

Hi

EV9.0.2

I need to rebuild journal archive index volumes from the beginning up to to a specific date (long story...)

Is there a SQL query so I can identify the dates of items in the  journal archive index volumes? I have about 550 of them. Don't need every single date of every item, just oldest and newest.

I know the GUI is meant to show this inthe From and To Date columns, but this seems innacurate.

thanks

Comments 10 CommentsJump to latest comment

Rob.Wilcox's picture

I'm not sure of how to do the SQL statement.. but how are you planning to do this ?

<snip>

I need to rebuild journal archive index volumes from the beginning up to to a specific date (long story...)

</snip>

A rebuild is a rebuild...  ie all of it.  And to rebuild 550 index volumes (if you're using the regular AVSMAXLOC) is going to take a LONNNNG time.

goatboy's picture

Hi, I am not planning on rebuilding the entire index, just specific index rollover volumes, right-click and rebuild in the VAC.

e.g. only these 2 out of 550

ID:120
Range:995563 - 1318587
Status:Normal
Location:N:\Enterprise Vault\Enterprise Vault Indexes\Index1
Total Items:158644
Failed Items:0
From  Date:12/31/1979 8:00:00 AM
To Date:11/14/2006 6:17:19 PM

ID:121
Range:1318588 - 1622033
Status:Normal
Location:E:\Enterprise Vault\Enterprise Vault Indexes\Index3
Total Items:273306
Failed Items:0
From  Date:12/31/1979 8:00:00 AM
To Date:12/1/2006 5:24:01 PM

But I only want to rebuild index volumes up to a certain date, hence the requirement for a SQL query. Thanks.

Rob.Wilcox's picture

hmm.. well if it's those two, why the concern over the date ranges?

What you can do is this ...

You know the index sequence number range...  you can do a select * from saveset where indexseqno = low-num

Then look at the IDDateTime, and the ArchivedDate fields.

Same for the highest index sequence number.

Would that help?

JesusWept3's picture

The simple query you would want is the following

USE EnterpriseVaultDirectory
SELECT A.ArchiveName "Archive Name", 
       IV.IndexVolumeIdentity "Index ID", 
       IV.FolderName "Index Folder", 
       IV.OldestItemDateUTC "Oldest Sent Item",
       IV.YoungestItemDateUTC "Newest Sent Item", 
       IV.OldestArchivedDateUTC "Item First Archived On",
       IV.YoungestArchivedDateUTC "Item Last Archived On",
       IV.IndexedItems "Item Count"
FROM   Archive A, Root R, IndexVolume IV
WHERE  IV.RootIdentity = R.RootIdentity
  AND  R.RootIdentity = A.RootIdentity
ORDER BY A.ArchiveName, IV.IndexVolumeIdentity
SOLUTION
John Santana's picture

Thanks Jesus for sharing the SQL script,

What does it means when there is duplicate name in the "Archive name" ?

Kind regards,

John Santana
IT Professional

--------------------------------------------------

Please be nice to me as I'm newbie in this forum.

TonySterling's picture

Probably means you have two Archives with the same name.  If you look in the Vault Admin Console at the archives do you see two with that name?

John Santana's picture

Yes I saw that Tony that tehre are two identical Archived mailboxes, but somehow in the Exchange Server is just one ?

Kind regards,

John Santana
IT Professional

--------------------------------------------------

Please be nice to me as I'm newbie in this forum.

TonySterling's picture

So you will need to determine if one is empty or if they both have data for the user in it.

If one is empty you can just delete it, if both have items in them then it gets a bit more complicated but nothing that can't be sorted. 

If you need more help just open a new thread. 

Cheers,