cancel
Showing results for 
Search instead for 
Did you mean: 

EV9 SQL query to get archive size base don age

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

 

Hello all, especially SQL guru's..,

I am asked if I can get the size of an archive when the items are older than 1 year, 3 years and 5 years. This relates to the possibility that I need to export archives to pst's, which will be imported in a seperate Exchange environment.

In other words, they want to know for Archive A:
What is the current size? I used usage.asp to get this.
What is the size when exporting items older than 1 year, older than 3 years, older than 5 years?
I found a query I thought might help, but I am not sure. That query is below:

SELECT A.ArchiveName, COUNT(S.IdTransaction) AS ArchivedItemCount, SUM(S.ItemSize/1024) AS ArchivedItemSize
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
linkedserver.mailstoredatabase.dbo.ArchivePoint AP,
linkedserver.mailstoredatabase.dbo.Saveset S

WHERE
(A.ArchiveName = archivename' )
AND S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointId = R.VaultEntryID
AND R.RootIdentity = A.RootIdentity
AND S.ArchivedDate > dateAdd("m",-1,getDate())
AND S.ArchivedDate < getDate()
GROUP BY A.ArchiveName

As far as I see, this will give me the size of archived items for the period 'today until today minus 1 month)

Does anyone know how to get it so the S.archiveddate is < 12 months?
Is that simply only using AND S.ArchivedDate < dateAdd("m",-12,getDate()),

or is it something else? 

Thanks!

Gertjan

Regards. Gertjan
1 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified
Hehe that looks like one of my queries!! But yeah it really is that simple, just Change m (months) to y (years) and then change the. Umber accordingly
https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

5 REPLIES 5

JesusWept3
Level 6
Partner Accredited Certified
Hehe that looks like one of my queries!! But yeah it really is that simple, just Change m (months) to y (years) and then change the. Umber accordingly
https://www.linkedin.com/in/alex-allen-turl-07370146

Andrew_Clare
Level 3
Partner

Hi Gertjan,

It sounds like something TransVault Migrator can do out of the box whilst gathering, then automate the migration of the mails that fit the age policy directly to the exchange target mailbox / archive with a full audit trail and no copying PSTs.  You may even be working on a site with TransVault already installed...

Regards,

Andrew

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

Hello Andrew,

True in both ways. However, customer is reluctant to pay for the tv-license, and claims exporting from ev is 'cheaper'. More time consuming but cheaper...

Thanks anyway!

Regards. Gertjan

Andrew_Clare
Level 3
Partner

Thanks for the feedback Gertjan!  If they change their mind please let me know.

regards,

Andrew

MichelZ
Level 6
Partner Accredited Certified

Archive Shuttle would be cheaper than TVS? :)


cloudficient - EV Migration, creators of EVComplete.