12-16-2010 06:08 AM
Hi Everyone,
Does enyone have any SQL queries ? looking for two to do the following:-
1. List number of mailboxes enabled for archiving.
2. List all mailboxes that are enabled for archiving with a archive of less that 50MB.
Thanks team
Pat
Solved! Go to Solution.
12-17-2010 03:59 AM
12-16-2010 06:28 AM
List all those enabled for Archiving
USE EnterpriseVaultDirectory SELECT MbxDisplayName "Mailbox Name" FROM ExchangeMailboxEntry WHERE MbxArchivingState =1 AND DefaultVaultID IS NOT NULL
List Archives enabled and under 50mb
SELECT EME.MbxDisplayName "Mailbox Name", AP.ArchivedItemsSize/1024 "Archive Size (MB)" FROM EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME, yourVaultStore.dbo.ArchivePoint AP WHERE AP.ArchivePointId = EME.DefaultVaultId AND EME.MbxArchivingState = 1 AND AP.ArchivedItemsSize < 51200
For the above you will need to run that for each vault store, replaced yourVaultStore.dbo, with the physical database name for your Vault Store database
12-16-2010 06:59 AM
The first query list all teh archives I just want the total number of mailboxes that are enabled for archiving please
12-16-2010 07:04 AM
That is what the first query does. If a mailbox archiving state is 1 that means it is enabled.
Do you mean you just want a count?
12-16-2010 07:28 AM
Yes please
12-16-2010 07:35 AM
SELECT COUNT(*) FROM ExchangeMailboxEntry WHERE MbxArchivingState =1 AND DefaultVaultID IS NOT NULL
12-17-2010 03:03 AM
Msg 208, Level 16, State 1, Line 1
Invalid object name 'VaultStoreEntry.dbo.ArchivePoint'.
On second script - also want to modify script to say if mailboxis enabled for archiving and size is 50MB or less then set MbxArchivingstate=2 in other words disable archiving for all mailboxes of 50mb or less
=2+ ===
12-17-2010 03:59 AM