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

EV90 - query to find which mailarchive is growing fast.

Created: 09 Apr 2013 • Updated: 10 Apr 2013 | 4 comments
GertjanA's picture
This issue has been solved. See solution.

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

 

 

Operating Systems:

Comments 4 CommentsJump to latest comment

Rob.Wilcox's picture

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 ;-)

JesusWept3's picture

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

SOLUTION
GertjanA's picture

Thanks JW

Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS
Company: www.t2.nl

www.quadrotech-it.com

www.symantec.com/vision

ManishN's picture

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

Regards,

MN