cancel
Showing results for 
Search instead for 
Did you mean: 

how many data has been archived

Y1251
Level 4

Hi,

For file archive, May I know is any sql query can check how many number of item & size has been archive yesterday?

1 REPLY 1

GertjanA
Level 6
Partner    VIP    Accredited Certified

Hello Ryan,

I am not sure the query I have works, but it is worth a try. I use it for Journal and Mailbox archiving. Run it against the Vault Store. In the Daily query, this section "dateadd("dd", -31, getdate ())" means it takes the current date, and then shows it for the last 32 days. If you need yesterday, use -2 instead of -31


--Yearly Rate
SELECT "Archived Date" = left (convert (varchar, archiveddate,20),4),"Yearly Rate" = count (*),"MB (orig)" = sum (originalsize)/1024/1024,"MB (in EV)" = sum (itemsize)/1024
from saveset with (nolock) inner join savesetproperty with (nolock) ON saveset.savesetidentity = savesetproperty.savesetidentity
where archiveddate > dateadd("YY", -1, getdate ())
group by left (convert (varchar, archiveddate,20),4)
order by "Archived Date" desc

--Monthly Rate
SELECT "Archived Date" = left (convert (varchar, archiveddate,20),7),"Monthly Rate" = count (*),"MB (orig)" = sum (originalsize)/1024/1024,"MB (in EV)" = sum (itemsize)/1024
from saveset with (nolock) inner join savesetproperty with (nolock) ON saveset.savesetidentity = savesetproperty.savesetidentity
where archiveddate > dateadd("mm", -5, getdate ())
group by left (convert (varchar, archiveddate,20),7)
order by "Archived Date" desc

--Daily Rate
SELECT "Archived Date" = left (convert (varchar, archiveddate,20),10),"Daily Rate" = count (*),"MB (orig)" = sum (originalsize)/1024/1024,"MB (in EV)" = sum (itemsize)/1024
from saveset with (nolock) inner join savesetproperty with (nolock) ON saveset.savesetidentity = savesetproperty.savesetidentity
where archiveddate > dateadd("dd", -31, getdate ())
group by left (convert (varchar, archiveddate,20),10)
order by "Archived Date" desc

--Hourly Rate
SELECT "Archived Date" = left (convert (varchar, archiveddate,20),14),"Hourly Rate" = count (*),"MB (orig)" = sum (originalsize)/1024/1024,"MB (in EV)" = sum (itemsize)/1024
from saveset with (nolock) inner join savesetproperty with (nolock) ON saveset.savesetidentity = savesetproperty.savesetidentity
where archiveddate > dateadd("hh", -24, getdate ())
group by left (convert (varchar, archiveddate,20),14)
order by "Archived Date" desc

 

Regards. Gertjan