Video Screencast Help
Scheduled Maintenance: Symantec Connect is scheduled to be down Saturday, April 19 from 10am to 2pm Pacific Standard Time (GMT: 5pm to 9pm) for server migration and upgrades.
Please accept our apologies in advance for any inconvenience this might cause.

SQL query to delete EV archive or change ArchivingState (MbxArchivingState = 1 to MbxArchivingState = 4)?

Created: 08 Nov 2010 • Updated: 09 Nov 2010 | 4 comments
This issue has been solved. See solution.

SQL query to delete EV archive or change ArchivingState (MbxArchivingState = 1 to MbxArchivingState = 4)?
 

Comments 4 CommentsJump to latest comment

JesusWept3's picture

it would be

USE EnterpriseVaultDirectory
UPDATE Archive
SET ArchivingState = 4
WHERE ArchivingStating = 1
AND RootIdentity = (SELECT A.RootIdentity FROM Archive A, Root R, ExchangeMailboxEntry EME WHERE EME.DefaultVaultID = R.VaultEntryID AND R.RootIdentity = A.RootIdentity AND EME.mbxDisplayName = 'my User')

Just a few things though,

1. this is not a supported script and won't be supported by symantec and especially EV Support.
2. Make sure you back up your Databases (Vault Store and EnterpriseVaultDirectory) before running this script
3. You do *NOT* want to just update every single archive that you want deleted all at one time, you need to work out a way to feed this through, several at a time and add more as they're deleted, otherwise you will simply overwhelm the servers
4. You will either have to restart your storage service or put your vault stores in and out of backup mode to get the deletion process to kic off
5. These archives that you want to delete may have items in a vault stores legal hold table or VaultInterests table that will stop the archive or its items from being deleted
6. These archives that you want to delete may have items that belong to a retention category where user deletions are not allowed, you will have to take that provision off of the retention category until the archive is deleted (during this time people may end up deleting items they shouldn't be allowed to delete)
7. You may face some issues depending on what storage medium you use, for instance if you have a compliance device, such as a Centera, you may not be able to delete items until the centeras own retention has expired on that particular item
8. I would suggest keeping a list of which users you're deleting when and where to make sure you can account for who's gone and when in case it comes up later request (say from a legal team)
9. I'd also suggest exporting the archives to a PST file initially so that if you delete someone by accident (it happens, and if you're deleting thousands, it probably will happen) you can have the data readily available.

But really, test this in a test lab, such as VMWare and make sure you understand how it works, the risks, the performance etc, how to recover items that have been accidentally deleted etc.

Also as i advised korbyn, put this in the Ideas section or upvote an existing Idea as i've heard this question more and more and more these days of wanting to mass delete archives

Just be sure that you test test test test test and test and get your process locked down before you start risking live EV data that could possibly have a lot of legal ramiffications

SOLUTION
JamaliP's picture

are you updating the "archivestatus" column under the archive table? Im not sure what the "ArchivingStating" colums is.