SQL script to count daily archive ingest rate in Megabytes

[John_Santana]

Hi All,

Can anyone here please assist me in modifying the following SQL script below ?

    SELECT "Archived Date" = LEFT (CONVERT (VARCHAR, archiveddate, 20), 10),
           "Daily Rate" = Count (*),
           "Av Size" = Sum (itemsize) / Count (*)
    FROM   saveset
    WHERE  archiveddate BETWEEN '2014-07-01' AND '2014-08-06'
    GROUP  BY LEFT (CONVERT (VARCHAR, archiveddate, 20), 10)
    ORDER  BY "archived date" DESC

So that it can display the total amount of Megabytes for all item archived between the date supplied.

The script above does not explains as to why the Open VaultStore partition lose 3 GB since yesterday.

Thanks in advance.

[AndrewB · Accepted solution]

here's a different script you can try. run it against your vault store database:

select "Archived Date" = left (convert (varchar, S.archiveddate,20),10),
"Daily Rate" = count (*),
"Avg Item Size" = sum (S.itemsize)/count (*),
SUM(SP.OriginalSize)/1024/1024 "Original Size (MB)"
FROM   Saveset S,
       SavesetProperty SP
WHERE  S.SavesetIdentity = SP.SavesetIdentity
AND archiveddate between '2014-08-01' and '2014-08-05'
group by left (convert (varchar, archiveddate,20),10)
order by "Archived Date" desc

---

as for why your open partition has 3gb less since yesterday there could be a few reasons:

are you running expiry?

are users allowed to delete?

are you running collections?

maybe you had lots of CABs expanded due to a discovery/search/export/restore and the cleanup process ran?

[John_Santana]

Thanks for the quick reply Andrew:

are you running expiry?

No there is no expiry set in all VaultStore partition.

are users allowed to delete?

No users not allowed to delete anything.

are you running collections?

Yes I am

maybe you had lots of CABs expanded due to a discovery/search/export/restore and the cleanup process ran?

Not sure how does the cleanup process actually consuming disk space ?

The behaviour is that last week, the open VaultStore partition usage was gradually increase up to 95% and then it drop back down to 80% usage for unknown reason.

[AndrewB]

you said your drive size went down by 3GB. i explained how the collections cleanup process might be what's responsible for reclaiming the space for you. here's a brief overview how:

files are collected into CABs but let's say you have 1000 emails in a CAB and you need one, EV has to extract all the emails from the CAB in order to get you the 1 file you wanted to restore. some time later on, the cleaup process comes along and sees that you have a bunch of extracted files that you dont need. it deletes them and frees up the space.

[John_Santana]

Hi Andrew,

Sorry for the confusion, the "free disk space" went down 3 GB so the disk space usage went up 3 GB higher than yesterday.

[AndrewB]

maybe you had lots of CABs expanded due to a discovery/search/export/restore and the cleanup process HASNT kicked in yet?