SQL assistance gathering EV information

[M__Urban]

Since there are no canned reports that can provide this information - are there any SQL gurus who could help write a quick query (2008 sql) that would provide summary information by vault user on the total number and total size of email that will be deleted if we would turn on our storage expiry/deletion task.  Were running EV8.0 SP3

Any help would be greatly appreciated.

[JesusWept3 · Accepted solution]

here's a query that i wrote some time ago, should be a good starting point, total size can be misleading though because whats on the db will not match whats on the disk (SIS, compression, collected files etc)

 - Change EVVaultStore to your Vault store database name
 - This is based on Modified Date (Sent date of the email) if you want it to do ArchivedDate then change S.IdDateTime to S.ArchivedDate
 - This is based on a '1 year' retention category, change the RCE.RetentionCategoryName = '1 Year' to another retention period
 - Also if you change the retention category, make sure you change the following S.IdDateTime < DateAdd(year, -1, GetDate())

This needs to be run per Vault Store that you have.

SELECT A.ArchiveName, COUNT(DISTINCT(S.IdTransaction)) AS ItemsToExpire, sum(s.ItemSize) AS TotalItemSize, RCE.RetentionCategoryName

FROM EnterpriseVaultDirectory.dbo.Archive A,
            EnterpriseVaultDirectory.dbo.Root R,
            EnterpriseVaultDirectory.dbo.RetentionCategoryEntry RCE,
            EVVaultStore.dbo.Saveset S,
            EVVaultStore.dbo.ArchivePoint AP,
            EVVaultstore.dbo.HoldSaveset HS

WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND AP.ArchivePointId = R.VaultEntryId
  AND R.RootIdentity = A.RootIdentity
  AND S.RetentionCategoryIdentity = RCE.RetentionCategoryIdentity
  AND S.SavesetIdentity != HS.SavesetIdentity
  AND S.ArchivePointIdentity != HS.ArchivePointIdentity
  AND S.IdDateTime < DateAdd(year, -1, GetDate())
  AND RCE.RetentionCategoryName = '1 Year'
  AND A.DeleteExpiredItems = 1

GROUP BY A.ArchiveName, RCE.RetentionCategoryName
ORDER BY A.ArchiveName

[JesusWept3]

Oh and also note that this query is a monstor, in a saveset table with 96 million records it took just over 15 minutes to run

[M__Urban]

Thanks for your help...I tried it out quickly and kept getting no result although the query ran successfully.  Just to confirm the Retention Category name would be exactly then name used for example:  Default Retention Category. 

[JesusWept3]

Yup, so lets say you are doing "Default Retention Category" which has a 3 year retention, and you are expiring based on ArchivedDate, you would make the following changes

From:
  AND S.IdDateTime < DateAdd(year, -1, GetDate())
  AND RCE.RetentionCategoryName = '1 Year'

To:
AND S.ArchivedDate < DateAdd(year, -3, GetDate())
  AND RCE.RetentionCategoryName = 'Default Retention Category'

Remember though the difference between Archived Date and Modified Date.
Modified Date is the date that the item was sent (or the actual modified date if you use FSA)
Archived Date was the date that the item was archived

So lets say you have an email that was sent (read: modified) on 06/01/2007 (mm/dd/yyyy) and it was archived on 01/01/2010
If you were to run the report using idDateTime (modified date) with a retention of three years, then you would not see the item set to expire until 06/01/2010
If you were to run the report using ArchivedDate with a retention of three years, then you would not see the item set to expire until 01/01/2013

Another example is an item sent on 01/01/2001 and archived on 01/01/2010,
by modified it would show up immediately (as it was eligible to expire on 01/01/2004)
by Archived Date it would show up on 01/01/2013

Other things to note about this query is that it excludes archives that are set to disable them from the expiry process, and also excludes items that are on legal hold (set by Discovery Accelerator)

What i would suggest is running expiry in report mode and look at the numbers it produces as they should be similar to the query above

[M__Urban]

SELECT A.ArchiveName, COUNT(DISTINCT(S.IdTransaction)) AS ItemsToExpire, sum(s.ItemSize) AS TotalItemSize, RCE.RetentionCategoryName

Below is my updated query...we use modified date so i left that section alone.  I have also pasted the result from running expiry in report mode:

Vault Store Name: Mailbox Archiving Store
Vault Store Id: 1757AAFC1C9B1B74987BDA6BDBE240E881210000evserver1
 
Number of vaults processed: 931
Number of vaults enabled for expiry: 931
Total number of expired items: 84663
 
Items marked with sent\received date based retention categories have been deleted:
Default Retention Category (3 years) : 74609 items.
Exception Retention Category (7 years) : 10054 items.
 
Items marked with archived date based retention categories have been deleted:
(none)

======================================

FROM EnterpriseVaultDirectory.dbo.Archive A,

EnterpriseVaultDirectory.dbo.Root R,
EnterpriseVaultDirectory.dbo.RetentionCategoryEntry RCE,
EVVSMailboxArchivingStore_1.dbo.Saveset S,
EVVSMailboxArchivingStore_1.dbo.ArchivePoint AP,
EVVSMailboxArchivingStore_1.dbo.HoldSaveset HS

WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointId = R.VaultEntryId
AND R.RootIdentity = A.RootIdentity
AND S.RetentionCategoryIdentity = RCE.RetentionCategoryIdentity
AND S.SavesetIdentity != HS.SavesetIdentity
AND S.ArchivePointIdentity != HS.ArchivePointIdentity
AND S.IdDateTime < DateAdd(year, -3, GetDate())
AND RCE.RetentionCategoryName = 'Default Retention Category'
AND A.DeleteExpiredItems = 1

GROUP BY A.ArchiveName, RCE.RetentionCategoryName
ORDER BY A.ArchiveName

[M__Urban]

SELECT A.ArchiveName, COUNT(DISTINCT(S.IdTransaction)) AS ItemsToExpire, sum(s.ItemSize) AS TotalItemSize, RCE.RetentionCategoryName

Below is my updated query...we use modified date so i left that section alone.  I have also pasted the result from running expiry in report mode:

Vault Store Name: Mailbox Archiving Store
Vault Store Id: 1757AAFC1C9B1B74987BDA6BDBE240E881210000evserver1
 
Number of vaults processed: 931
Number of vaults enabled for expiry: 931
Total number of expired items: 84663
 
Items marked with sent\received date based retention categories have been deleted:
Default Retention Category (3 years) : 74609 items.
Exception Retention Category (7 years) : 10054 items.
 
Items marked with archived date based retention categories have been deleted:
(none)

======================================

FROM EnterpriseVaultDirectory.dbo.Archive A,

EnterpriseVaultDirectory.dbo.Root R,
EnterpriseVaultDirectory.dbo.RetentionCategoryEntry RCE,
EVVSMailboxArchivingStore_1.dbo.Saveset S,
EVVSMailboxArchivingStore_1.dbo.ArchivePoint AP,
EVVSMailboxArchivingStore_1.dbo.HoldSaveset HS

WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointId = R.VaultEntryId
AND R.RootIdentity = A.RootIdentity
AND S.RetentionCategoryIdentity = RCE.RetentionCategoryIdentity
AND S.SavesetIdentity != HS.SavesetIdentity
AND S.ArchivePointIdentity != HS.ArchivePointIdentity
AND S.IdDateTime < DateAdd(year, -3, GetDate())
AND RCE.RetentionCategoryName = 'Default Retention Category'
AND A.DeleteExpiredItems = 1

GROUP BY A.ArchiveName, RCE.RetentionCategoryName
ORDER BY A.ArchiveName

[JesusWept3]

What version of SQL are you running?
and is there any chance you can paste a screenshot of the no results?

[M__Urban]

Were running SQL 2008 64bit standard (0 row(s) affected)

[JesusWept3]

hang on wait, 0 rows affected?!
you should only get that from an update script not a select
the select should show you the column headers

anywho, can you run the following two statements for me and paste what they come back with?

PRINT GetDate()
PRINT DateAdd(year, -3, GetDate())

You should get the following:

Apr 6 2010, 9:04PM
Apr 6 2007, 9:04PM

Also try making the following changes to remove the checks for items on hold and for archives that are disabled from expiry
it's the only thing that really makes sense, but anywho, here are the changes


From:
AND S.SavesetIdentity != HS.SavesetIdentity
AND S.ArchivePointIdentity != HS.ArchivePointIdentity
AND S.IdDateTime < DateAdd(year, -3, GetDate())
AND RCE.RetentionCategoryName = 'Default Retention Category'
AND A.DeleteExpiredItems = 1

To:
-- AND S.SavesetIdentity != HS.SavesetIdentity
-- AND S.ArchivePointIdentity != HS.ArchivePointIdentity
AND S.IdDateTime < DateAdd(year, -3, GetDate())
AND RCE.RetentionCategoryName = 'Default Retention Category'
-- AND A.DeleteExpiredItems = 1

[M__Urban]

Here is the response I get from the print commands - that part looks fine.  I will try the rest and let you know.  Thanks alot for all your help.

Apr 7 2010 8:21AM

Apr 7 2007 8:21AM

[M__Urban]

I get the column headers but no results...