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
Discussion Filed Under:
Comments 10 Comments • Jump to latest comment
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.
Many Thanks,
Rob
www.quadrotech-it.com - All your EV Tools
PS I hope that the post proves helpful.
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.
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?
Many Thanks,
Rob
www.quadrotech-it.com - All your EV Tools
PS I hope that the post proves helpful.
That works pefectly, thanks very much.
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.IndexVolumeIdentityThanks Jesus for sharing the SQL script,
What does it means when there is duplicate name in the "Archive name" ?
Kind regards,
John Santana
Graduate IT Professional
--------------------------------------------------
Please be nice to me as I'm newbie in this forum.
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?
Tony Sterling
www.bluesource.net or www.bluesource.co.uk
Offices in the US and the UK
even better, thanks very much!
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
Graduate IT Professional
--------------------------------------------------
Please be nice to me as I'm newbie in this forum.
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,
Tony Sterling
www.bluesource.net or www.bluesource.co.uk
Offices in the US and the UK
Would you like to reply?
Login or Register to post your comment.