SQL Query to find archived messages & archive item size in a specified period?

[rajesh_velagapu]

1) SQL Query to find archived messages & archive item size in a specified period?

  And

 2) EV server has 2 differnt mailbox archiving tasks, SQL query to find archive items and
    size for each task (each mailbox server) in a specified period?

 Example: SQL Query to find archived messages processed in a specified period? 

select count(*) as '4/12'

from saveset

where archiveddate >= '4/12/2011' and archiveddate < '4/13/2011'

[RahulG]

SELECT SUM(ItemSize) AS Expr1, MIN(ArchivedDate) AS Expr2, MAX(ArchivedDate) AS Expr3
FROM Saveset

WHERE (ArchivedDate > '2010-01-01 18:00') AND (ArchivedDate < '2010-06-01 5:00')

The stored procedure usps_gettablesize saveset can be used to get the number of archived items stored in a specific Vault Store

[TonySterling]

few questions,

What version of EV? 

How many EV Servers?

How many Vault Stores?

[rajesh_velagapu]

What version of EV? EV9 SP1

How many EV Servers? 2

How many Vault Stores? 2

[rajesh_velagapu]

Thanks!

2) EV server has 2 differnt mailbox archiving tasks, SQL query to find archive items and
    size for each task (each mailbox server) in a specified period?

[TonySterling]

So if each mailbox task uses a different vault store it is pretty easy, just run this against each vault store database:

-- This one gives a hourly rate:
--Runs against the VaultStore Database
select "Archived Date" = left (convert (varchar, archiveddate,20),14),
"Hourly Rate" = count (*),
"Av Size" = sum (itemsize)/count (*)
from saveset
where archiveddate between '2000-05-01' and '2006-07-31'
group by left (convert (varchar, archiveddate,20),14)
order by "Archived Date" desc

-- This one gives a daily rate:
--Runs against the VaultStore Database
select "Archived Date" = left (convert (varchar, archiveddate,20),10),
"Daily Rate" = count (*),
"Av Size" = sum (itemsize)/count (*)
from saveset
where archiveddate between '2004-05-01' and '2006-05-31'
group by left (convert (varchar, archiveddate,20),10)
order by "Archived Date" desc

[rajesh_velagapu]

Thanks Tony!!!

We have 2 mailbox servers (2 tasks on the EV server) pointing to same vault store. 

[RahulG]

I am not sure , but I think it is not possible to get the query to 2nd question.

[JesusWept3 · Accepted solution]

This query should work for you

SELECT ESE.ExchangeComputer "Exchange Server", 
       COUNT(S.IdTransaction) "Items Archived",
       SUM(S.ItemSize)/1024 "Size of Items (MB)"
FROM   EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
       EnterpriseVaultDirectory.dbo.ExchangeMailboxStore EMS,
       EnterpriseVaultDirectory.dbo.ExchangeServerEntry ESE,
       yourVaultStore.dbo.ArchivePoint AP,
       yourVaultStore.dbo.Saveset S
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = EME.DefaultVaultId
  AND  EME.MbxStoreIdentity = EMS.MbxStoreIdentity
  AND  EMS.ExchangeServerIdentity = ESE.ExchangeServerIdentity 
  AND  S.ArchivedDate > DATEADD(d, -1, getDate())
GROUP BY ESE.ExchangeComputer

[rajesh_velagapu]

Do I have to run this for each mailbox store?

Can I specify date?

[JesusWept3]

run it against each Vault Store, you are going to have to change the "yourVaultStore.dbo...." to the name of your actual vault store database name. and if you have 12 vault store databases, then you will have to run it 12 times , each time changing the yourVaultStore to the db name of your vault store database.

As for the dates, just change

AND  S.ArchivedDate > DATEADD(d, -1, getDate())

To

AND S.ArchivedDate > '2012-01-01 00:00:00.0000' AND S.ArchivedDate < '2012-01-31 23:59:59.0000'

That would give you all the items archived for the month of january 2012 on all your exchange servers that have been archived by that vault store

[TonySterling]

Yes, you need to modify these lines:

       yourVaultStore.dbo.ArchivePoint AP,
       yourVaultStore.dbo.Saveset S

[rajesh_velagapu]

Thanks All!!!