07-21-2011 02:48 AM
Dear All,
Is it possible to run a SQL query or EVPM script that will set a limit on all the user archives, based on current size?
For instance:
If limit set already on an archive – leave as is
If user archive < 1GB, set 1GB size limit
If user archive xGB, set xGB + 200MB size limit
Environment
Enterprise Vault 8.0 SP4
Exchange server 2007 SP2
SQL Server 2008
Thanks in advance.
Solved! Go to Solution.
07-25-2011 09:43 AM
OK so the query would look something like this
You'll need to run each query against each vault store
(The Archive limit is set in the EnterpriseVaultDirectory database, the archive size is held in the Vault Store Database)
So remember to change "yourVaultStore" to your vault store name, if you have multiple vault stores, just change the name each time and carry on
The following sets a 1GB limit where users are under 1GB and no quota has been set
UPDATE Archive
SET ArchiveLimitSize = 1048576
WHERE RootIdentity IN
(SELECT R.RootIdentity
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
yourVaultStore.dbo.ArchivePoint AP
WHERE AP.ArchivePointId = R.VaultEntryId
AND R.RootIdentity = A.RootIdentity
AND A.ArchiveLimitSize = 0
AND AP.ArchivedItemsSize < 1048576)
The other query requested, to take current Archive size, +200MB and set that, would be painful, you'd have to do some kind of VBScript to loop over each user, get the users current size that is > 1048575, then add 204800 to it and then update the Archive table based on their rootidentity and change the ArchiveLimitSize to be that number you've just gotten
07-25-2011 09:43 AM
OK so the query would look something like this
You'll need to run each query against each vault store
(The Archive limit is set in the EnterpriseVaultDirectory database, the archive size is held in the Vault Store Database)
So remember to change "yourVaultStore" to your vault store name, if you have multiple vault stores, just change the name each time and carry on
The following sets a 1GB limit where users are under 1GB and no quota has been set
UPDATE Archive
SET ArchiveLimitSize = 1048576
WHERE RootIdentity IN
(SELECT R.RootIdentity
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
yourVaultStore.dbo.ArchivePoint AP
WHERE AP.ArchivePointId = R.VaultEntryId
AND R.RootIdentity = A.RootIdentity
AND A.ArchiveLimitSize = 0
AND AP.ArchivedItemsSize < 1048576)
The other query requested, to take current Archive size, +200MB and set that, would be painful, you'd have to do some kind of VBScript to loop over each user, get the users current size that is > 1048575, then add 204800 to it and then update the Archive table based on their rootidentity and change the ArchiveLimitSize to be that number you've just gotten
07-25-2011 10:50 PM
very nice JW. i suppose if the requirements arent too complex, he could have this script run once a day to impose the quota once an archive reaches the predetermined size.
07-26-2011 02:21 AM
Good bit of SQL ... very nice.
07-26-2011 03:34 AM
Thanks a lot JesusWept2. Will apply this and update you the results.