01-26-2011 01:39 PM
Currently we retain all items in archives forever. I have been asked to provide reports based on the following.
Size of the data in the archives that is 3 - 5 years old.
Size of the data in the archives that is 5 - 7 years old.
Size of the data that is older than 7 years.
Whats the best way to go about getting this data? We are running EV9.
Thanks,
Gerald
Solved! Go to Solution.
01-27-2011 11:56 AM
try the following
SUM(CAST(ItemSize AS bigint))/1024
01-26-2011 01:48 PM
I think you would need something like
Items between 3-5 years
USE yourVaultStoreDB SELECT COUNT(IdTransaction) "No. of Archived Items", SUM(ItemSize)/1024 "Size of Items (MB)" FROM Saveset WHERE ArchivedDate >= DATEADD(year, -5, getDate()) AND ArchivedDate <= DATEADD(year, -3, getDate())
Items between 5-7 years
USE yourVaultStoreDB SELECT COUNT(IdTransaction) "No. of Archived Items", SUM(ItemSize)/1024 "Size of Items (MB)" FROM Saveset WHERE ArchivedDate >= DATEADD(year, -7, getDate()) AND ArchivedDate <= DATEADD(year, -5, getDate())
Items that are older than 7 years
USE yourVaultStoreDB SELECT COUNT(IdTransaction) "No. of Archived Items", SUM(ItemSize)/1024 "Size of Items (MB)" FROM Saveset WHERE ArchivedDate < DATEADD(year, -7, getDate())
Replace "USE yourVaultStore" with the name of your vault store database.
This reports on the Archive Date of the item, so if you archive an item from 2003 yesterday, that number would not show up, because it had only just been archived
If you want a report of items that have been Sent/Received instead of Archived, then it would be
WHERE IdDateTime < DATEADD(year, -7, getDate())
01-26-2011 01:52 PM
Thanks! I will give this a try.
01-27-2011 05:37 AM
@JW2 - Hi Do you think you could modify the SQL slightly do it can list out the data by year?
So doing the current year, which giving we are in January will be quite low, and then the values for 2010,2009,2008 etc for as many years as the EV organisation has been active.
Thanks,
Jeremy.
01-27-2011 08:57 AM
The Queries seem to work. Thanks. Only issue is that on some of the databases give the error below.
Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.
01-27-2011 09:07 AM
ok change SUM(S.ItemSize)/1024 "Item Size (MB)" to SUM(S.ItemSize)/1024/1024 "Item Size (GB)"
01-27-2011 09:34 AM
OK. My SQL is a bit rusty. Don't I have to have something else in the query in order to use the s.itemsize?
01-27-2011 10:06 AM
well thats what the SUM(S.ItemSize) is for
Basically what is happening is that the complete sum of itemsize in KB is too long/too big to hold in an int column
if you /1024 that will give you size in MB but even then its too big, so you do /1024/1024 for GB or /1024/1024/1024 for TB and so on and so forth, the more you divide, the smaller the number etc
01-27-2011 10:40 AM
I have this for the query.
SELECT COUNT(IdTransaction) "No. of Archived Items", SUM(s.ItemSize)/1024/1024 "Item Size (GB)"
FROM Saveset
WHERE IdDateTime >= DATEADD(year, -5, getDate())
AND IdDateTime <= DATEADD(year, -3, getDate())
I get this error. The Multi-part identifier "s.itemsize" could not be found.
01-27-2011 10:59 AM
my bad it should be SUM(ItemSize)/1024/1024, remove the S from it
thats only valid if you alias your tables
i.e.
Select a.something
FROM aTable A
01-27-2011 11:48 AM
Thanks. So far i still am getting the Arithmetic overflow error. I have added another /1024 twice, but still no joy. The particular VS I'm running this on is 13 TB in Total.
01-27-2011 11:56 AM
try the following
SUM(CAST(ItemSize AS bigint))/1024
01-28-2011 08:09 AM
Thanks!!. This gave me the data I needed. Thanks for your help!
01-29-2011 07:05 PM
JPR dude, i swear i'd given you this query before?
anywho, check it out
USE yourVaultStore
SELECT LEFT(convert(varchar, ArchivedDate,120),4) AS ArchivedDate,
COUNT(ArchivedDate) AS ArchivedAmount
FROM Saveset
GROUP BY LEFT(CONVERT(varchar, ArchivedDate,120), 4)
ORDER BY ArchivedDate ASC