I needed an easy way to look at the trends of EV archiving. Number of messages and sizes; So I created this VBScript around MichelZ's SQL script:
SELECT COUNT(*) AS Count, MIN(ArchivedDate) AS FirstArchive, MAX (ArchivedDate) AS LastArchive, SUM(ItemSize)/1024 AS SaveSetSize, SUM(OriginalSize)/1024/1024 AS OriginalSize
FROM Saveset INNER JOIN SavesetProperty ON Saveset.SavesetIdentity = SavesetProperty.SavesetIdentity
WHERE ArchivedDate > YesterdaysDate 00:00' AND ArchivedDate < 'YesterdaysDate 23:59'
I have scheduled this script to run each morning. It gets the previous day's values and writes them to a .csv file (per server) which I can look at from time to time.
This version is a simplified version of one I use. We have two database servers in our organisation, one in each datacentre. I am assuming most users will have one database server running all EV databases as this is the easiest way to go about things.
I have tried to make this script as short as possible and commented as much as possible. I would recommend opening it with a script editor such as notepad++ rather than plain notepad. Within the script you may need to change the following for your environment:
* I am assuming you have more than one database, if this is not the case then change the line to read:
strDatabases = Array("Your_DB_Database1")
Yes you are specifying an array for a single item, but just to make the change easy.
Choose a suitable connection string. The first one is commented out by the ' symbol.