Forum Discussion

Y1251's avatar
Y1251
Level 5
4 years ago

how many data has been archived

Hi,

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

  • 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