Is it possible to run a SQL statement that will set a limit on all the user archives, based on current size?

[Moorthipvm]

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.

[JesusWept3 · Accepted solution]

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

[AndrewB]

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.

[Rob_Wilcox1]

Good bit of SQL ... very nice.

[Moorthipvm]

Thanks a lot JesusWept2. Will apply this and update you the results.