Forum Discussion

John_Santana's avatar
John_Santana
Level 6
11 years ago

SQL script to count daily archive ingest rate in Megabytes

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.

Backing Up
Best Practice
Configuring
Documentation
Enterprise Vault
Information Governance
Tip-How to
Windows Server (2003-2008)
  • AndrewB's avatar
    AndrewB
    11 years ago

    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?

  • AndrewB's avatar
    AndrewB
    Moderator
    11 years ago

    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's avatar
    John_Santana
    Level 6
    11 years ago

    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's avatar
    AndrewB
    Moderator
    11 years ago

    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's avatar
    John_Santana
    Level 6
    11 years ago

    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's avatar
    AndrewB
    Moderator
    11 years ago

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