cancel
Showing results for 
Search instead for 
Did you mean: 

EV90 - query to find which mailarchive is growing fast.

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

Hello all, and especially SQL guru's

We found that for some reason one of our Vaultstores has grown with 12GB last night, where the average growth is about 2GB a night.

Is it possible to use a SQL query to find which archive(s) have a huge amount of data added in the last 24 to 48 hour? This to try to find if a user is doing something (ie manually importing a large PST, someone archiving lots of holidaypics) or if something is wrong.

Thanks.

Gertjan

 

 

Regards. Gertjan
1 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified
You could do something like SELECT A.ArchiveName, COUNT(S.idTransaction) "archived items", SUM(s.itemsize)/1024 "Archived item size" FROM EnterpriseVaulDirectory.dbo.Archive A, EnterpriseVaultDirectory.dbo.Root R, EVVSMailStore_1.dbo.Archivepoint AP, EVVSMailStore_1.dbo.Saveset S WHERE s.archivepointidentity = ap.archivepointidentity AND ap.archivepointid = r.vaultentryid AND r.rootIdentity = a.rootidentity AND s.archivedDate > DATEADD(d, -1, getdate()) AND SUM(s.itemsize)/1024 > 1024 GROUP BY A.ArchiveName ORDER BY SUM(s.itemSize)/1024 That will give you all archives in the mailstore vault store that have archived more than 1GB in 24 hours
https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

4 REPLIES 4

Rob_Wilcox1
Level 6
Partner

I'd suggest travelling back in time to the weekend, where you would be upgrading to EV 10.0.3, and then you'd be able to use the mailbox archiving report to see if it was that which had caused the extra influx of data ;)

Working for cloudficient.com

JesusWept3
Level 6
Partner Accredited Certified
You could do something like SELECT A.ArchiveName, COUNT(S.idTransaction) "archived items", SUM(s.itemsize)/1024 "Archived item size" FROM EnterpriseVaulDirectory.dbo.Archive A, EnterpriseVaultDirectory.dbo.Root R, EVVSMailStore_1.dbo.Archivepoint AP, EVVSMailStore_1.dbo.Saveset S WHERE s.archivepointidentity = ap.archivepointidentity AND ap.archivepointid = r.vaultentryid AND r.rootIdentity = a.rootidentity AND s.archivedDate > DATEADD(d, -1, getdate()) AND SUM(s.itemsize)/1024 > 1024 GROUP BY A.ArchiveName ORDER BY SUM(s.itemSize)/1024 That will give you all archives in the mailstore vault store that have archived more than 1GB in 24 hours
https://www.linkedin.com/in/alex-allen-turl-07370146

ManishN
Level 4
Employee Accredited

Gertjan,

Not sure if this query is helpful but see if this helps

select ArchiveName,count(*) as 'ItemsArchived' from
enterprisevaultdirectory.dbo.root,enterprisevaultdirectory.dbo.archive
,archivepoint ,saveset where archiveddate >'2012-04-09 00:00:00.000' and archiveddate < '2013-04-09 23:59:00.000'and
saveset.archivepointidentity = archivepoint.archivepointidentity and
enterprisevaultdirectory.dbo.root.vaultentryid = archivepoint.archivepointid
and
enterprisevaultdirectory.dbo.root.rootidentity=enterprisevaultdirectory.dbo.archive.rootidentity
group by archivename
order by archivename desc

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

Thanks JW

Regards. Gertjan