Find how many emails are archived over a certain date

[Greenroomboy]

Hi All,

We currently use EV 7 SP3 and as of now we have no retention/storage expiry limits.  We are thinking of implementing such things but i wondered if there was a way i can query the storage database to find out how many items are over a certain age so i can understand how many would be deleted if i were to implement.

for example i need to see how many emails we have that are older than 3 years old?

Can this be done?

Thanks

Steve

[EVNoodles]

You could try a SQL Query similar to this, to target a specific vault store \ stores

Select count(*),min(archiveddate),max(archiveddate)
from saveset where archiveddate >'2002-04-26 18:00'
and archiveddate < '2006-12-27 5:00'

[Greenroomboy]

Thanks EV Noodles.


I will ask my SQL guy to give it a blast and see what it comes back with.

Steve

[Wayne_Humphrey]

Greenroomboy,

Here are some nicer SQL scripts for you.

USE VSDB
SELECT
'Archived Date' = LEFT (convert (VARCHAR, ArchivedDate,20),13),
'VSNAME VS01 - Hourly Rate' = count (*),
'Size in MB' = SUM (ItemSize)/1024 FROM Saveset
WHERE
ArchivedDate > dateadd("hh", -24, getdate ())
GROUP BY
left (convert (VARCHAR, archivedDate,20),13)
ORDER BY
'Archived Date' DESC

Or
USE VSDB
SELECT COUNT (*)
AS 'VSNAME VS01'
FROM
Saveset
WHERE
ArchivedDate > dateadd("hh", -168, getdate ())

[Greenroomboy]

Thanks Wayne, very much apprectiate it.

Can i ask a huge favour, can you break down those SQL Scripts with the parameters i should use?  I am sure my SQL team will understand this hands down but for my own knowledge can you let me know what each line will do?

Hope thats not to much trouble.

Thanks

Steve

[Wayne_Humphrey · Accepted solution]

Hi Greenroomboy,

Lets explain:

To return the daily Archive Statistics per Vault sore to get the following information:

Archived Date

Vault Store Name

Size in MB

Run this SQL Script

-- SET VSDB to the relevant Vault Store Database

USE VSDB

-- This selects the relevant tables

SELECT

'Archived Date' = LEFT (convert (VARCHAR, ArchivedDate,20),13),

'VSNAME VS01 - Hourly Rate' = count (*),

'Size in MB' = SUM (ItemSize)/1024 FROM Saveset

WHERE

-- this is the time to fetch the items for e.g. older than 24h

ArchivedDate > dateadd("hh", -24, getdate ())

GROUP BY

left (convert (VARCHAR, archivedDate,20),13)

ORDER BY

'Archived Date' DESC

Or if you just want to see how many items where archived in the past week you could use something like this

-- SET VSDB to the relevant Vault Store Database

USE VSDB

-- Count the number of savsets in the table

SELECT COUNT (*)

AS '

VSNAME VS01'

FROM

Saveset

WHERE

-- where all data is older than 7 days.

ArchivedDate > dateadd("hh", -168, getdate ())