Highlighted

EV9 SQL query to get archive size base don age

 

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 Solution

Accepted Solutions
Highlighted
Accepted Solution!

Hehe that looks like one of

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
Highlighted
Accepted Solution!

Hehe that looks like one of

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

Highlighted

Hi Gertjan, It sounds like

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

Highlighted

yes yes

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
Highlighted

Thanks for the feedback

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

regards,

Andrew

Highlighted

Archive Shuttle would be

Archive Shuttle would be cheaper than TVS? Smiley Happy


Quadrotech - EV Migration and Office 365 Solutions, creators of Archive Shuttle.