Archive Report

[Gerald_2]

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

[JesusWept3]

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())

[Gerald_2]

Thanks! I will give this a try.

[jprknight-oldax]

@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.

[Gerald_2]

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.

[JesusWept3]

ok change SUM(S.ItemSize)/1024 "Item Size (MB)" to SUM(S.ItemSize)/1024/1024 "Item Size (GB)"

[Gerald_2]

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?

[JesusWept3]

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

[Gerald_2]

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.

[JesusWept3]

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

 

[Gerald_2]

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.

[JesusWept3 · Accepted solution]

try the following

 SUM(CAST(ItemSize AS bigint))/1024

[Gerald_2]

Thanks!!. This gave me the data I needed. Thanks for your help!

[JesusWept3]

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