Looking for a SQL Query to show all the archives that have zero items.

[dgeiger28]

Hi all,

I am not a SQL guys so sorry if this is a easy task, but i just havent' learned to writte SQL scripts.  I need to come up with a query to list all the archives that have zero items in them.  We probably have 1000's and i would like to get those cleaned up and removed.  So is it possible to run a SQL query to list out all of the archives with zero itmes?

Thanks

DG

[JesusWept3 · Accepted solution]

This is probably the easiest query, would need to be run on each vault store.
So change EVVSYourVaultStore_1 to the database name of your vault store

SELECT A.ArchiveName "Archive Name", 
       R.VaultEntryID "Archive ID",
       AP.ModifiedDate "Last Modified",
       AP.HighestIndexSeqNo "Highest Index Num",
       AP.ArchivedItems "Archived Items"
FROM   EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.Root R,
       EVVSYourVaultStore1_1.dbo.ArchivePoint AP
WHERE  AP.ArchivePointId = R.VaultEntryID
  AND  R.RootIdentity = A.RootIdentity
  AND  AP.ArchivedItems = 0

[dgeiger28]

Jesus thanks for the quick reply.  So if my mailbox vault store databases name is evmailboxvault the script would look like this correct?

SELECT A.ArchiveName "Archive Name",

       R.VaultEntryID "Archive ID",

       AP.ModifiedDate "Last Modified",

       AP.HighestIndexSeqNo "Highest Index Num",

       AP.ArchivedItems "Archived Items"

FROM   EnterpriseVaultDirectory.dbo.Archive A,

       EnterpriseVaultDirectory.dbo.Root R,

       evmailboxvault.dbo.ArchivePoint AP

WHERE  AP.ArchivePointId = R.VaultEntryID

  AND  R.RootIdentity = A.RootIdentity

  AND  AP.ArchivedItems = 0

[JesusWept3]

yup, you can run this query to find out the name of the databases too.
Just use the DatabaseDSN Name, and replace EVVSYourVaultStore1_1 with the name of the Database returned from this query
 

USE EnterpriseVaultDirectory
SELECT VaultStoreName, DatabaseDSN, SQLServer
FROM VaultStoreEntry
ORDER BY VaultStoreName 

[dgeiger28]

Thank you sir!

[dgeiger28]

Would there be a way to add the archive name in also?

[dgeiger28]

Nevermind found it.  Thanks again for all your help.

[John_Santana]

why is that the archive mailbox got 0 items ?

[JesusWept3]

Could be users that were enabled but never had anything archived or really old users that are no longer with the company and had all their items expire

[John_Santana]

Cool, so in this case it can be deleted safely from the Vault Admin Console one by one ?