In an EV 12.1 environment
Does anyone have an SQL script that will allow me to change the status of all existing mailbox archives that have 0 items?
I'm not sure how complex this would be but essentially I'd like to test a script that can identfy any archive that has 0 items so I can set these for deletion.
I think this will need to be made up of checking all ArchivePointIDs with 0 savesets and then to set the status of these archives to 4 (marked for deletion)
perhaps I'm way off the mark - but any help would be great.
I use below script to get an overview of the archives. This might be sufficient for you too. Change "VaultStore1" to refelct your Vault Store Database, but I assume you understand.
-- Shows archive information, items, original size of items and ev size of items
SELECT A.ArchiveName "Archive Name",
COUNT(S.IdTransaction) AS ArchivedItemCount,
SUM(S.ItemSize)/1024 "Compressed Size (MB)",
SUM(SP.OriginalSize)/1024/1024 "Uncompressed Size (MB)"
FROM EnterpriseVaultDirectory.dbo.Archive A,
WHERE A.RootIdentity = R.RootIdentity
AND R.VaultEntryId = AP.ArchivePointId
AND AP.ArchivePointIdentity = S.ArchivePointIdentity
AND S.SavesetIdentity = SP.SavesetIdentity
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName
I'm not in a position to run that just yet but will try it soon and see if that helps. I'm not great with SQL queries but will this just return the archives with 0 items? if not, should it be easy to edit show I just get those with 0 item returned in the results - becuase I then want to capture the archive name and set the status of these to status 4 as part of the same (or a subsequent) query.
This query will give you an overview of each archive. If you put it in an Excel, then you can sort on item count.
I'm not sure how to alter the query to only show archives with 0 items. As yourself, I am no SQL savvy person.
OK thanks - I aprreciate the input
I think I can get that info from from the usage report. I was hoping to create a SQL query along the lines of UPDATE Archive SET archivestatus = 4 WHERE <number of items> = 0 FROM <list if archivenames>
But its the SQL query piece to check that the archive has 0 items is where I'm struggling as I think it needs to involve looking at savesets and archiveIDs etc but I'm uncertain
However, thanks again for the help - i suspect it's noit a simple query that anyone would know.
this query wil list archive names + number of items archived:
( I would either test it myself or talk to Veritas before using it)
SELECT AV.ArchiveName, vAP.ArchivedItems
FROM view_ArchivePoint vAP INNER JOIN
EnterpriseVaultDirectory.dbo.ArchiveView AV ON AV.VaultEntryId = vAP.ArchivePointId
( and then insert it in your query)
Where (**Insert here**)
SET ArchiveStatus = 4
I have written some parts out of my head, so check the query before.
That's great - thanks very much.
I'm making a bit of progress here myself - I been able to create a table that maps the archive name to the archivepoint ID and number of items so I think I'm getting close now
There are two approaches to determining whether an archive has zero items.
1) Count up all the items in the Saveset table that belong to each archive. This is what Gertjan's query is doing. This is always going to be accurate, but may be time consuming to run.
2) Use the statistics in the ArchivePoint table. This table tracks the number and cumulative size of items in each archive. It is much faster to query this data than to count and sum from the Saveset table; however, while uncommon, it is possible for these statistics to be incorrect. (We have EVSVR operations to repair these statistics, which are referred to as "Reference Counts" or "RefCounts" in the EVSVR program text and documentation.)
Here is a statement to select all Archives with zero items, using the first method:
SELECT ap.ArchivePointId, av.ArchiveName, COUNT(s.SavesetIdentity) AS ItemCountFromSaveset FROM ArchivePoint AS ap JOIN EnterpriseVaultDirectory.dbo.ArchiveView AS av ON av.VaultEntryId = ap.ArchivePointId LEFT OUTER JOIN Saveset AS s ON s.ArchivePointIdentity = ap.ArchivePointIdentity GROUP BY ap.ArchivePointId, av.ArchiveName HAVING COUNT(s.SavesetIdentity) = 0
Here is a statement to select all Archives with zero items, using the second method:
SELECT ap.ArchivePointId, av.ArchiveName, ap.ArchivedItems FROM ArchivePoint AS ap JOIN EnterpriseVaultDirectory.dbo.ArchiveView AS av ON av.VaultEntryId = ap.ArchivePointId WHERE ap.ArchivedItems = 0
Here is a statement to mark for deletion all Archives with zero items, using the first method:
UPDATE EnterpriseVaultDirectory.dbo.ArchiveView SET ArchiveStatus = 4 WHERE VaultEntryId IN ( SELECT ap.ArchivePointId FROM ArchivePoint AS ap JOIN EnterpriseVaultDirectory.dbo.ArchiveView AS av ON av.VaultEntryId = ap.ArchivePointId LEFT OUTER JOIN Saveset AS s ON s.ArchivePointIdentity = ap.ArchivePointIdentity GROUP BY ap.ArchivePointId HAVING COUNT(s.SavesetIdentity) = 0 )
Here is a statement to mark for deletion all Archives with zero items, using the second method:
UPDATE EnterpriseVaultDirectory.dbo.ArchiveView SET ArchiveStatus = 1 FROM ArchivePoint AS ap JOIN EnterpriseVaultDirectory.dbo.ArchiveView AS av ON av.VaultEntryId = ap.ArchivePointId WHERE ap.ArchivedItems = 0
Hope this helps you out.