EV SQL script to change archive status for all archives with 0 item count

Hi

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.

kind regards

8 Replies

Re: EV SQL script to change archive status for all archives with 0 item count

Hello,

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,
       EnterpriseVaultDirectory.dbo.Root R,
       VaultStore1.dbo.ArchivePoint AP,
       VaultStore1.dbo.Saveset S,
       VaultStore1.dbo.SavesetProperty SP

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

Regards. Gertjan

Re: EV SQL script to change archive status for all archives with 0 item count

Thanks Gertjan

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.

thanks again

Re: EV SQL script to change archive status for all archives with 0 item count

Hello,

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.

Regards. Gertjan
Highlighted

Re: EV SQL script to change archive status for all archives with 0 item count

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.

cheers

Re: EV SQL script to change archive status for all archives with 0 item count

 

Hi,

this query wil list archive names + number of items archived:

( I would either  test it myself or talk to Veritas before using it)

USE VaultStoreDatabase 
SELECT AV.ArchiveName, vAP.ArchivedItems
FROM view_ArchivePoint vAP INNER JOIN
EnterpriseVaultDirectory.dbo.ArchiveView AV ON AV.VaultEntryId = vAP.ArchivePointId
Where vAP.ArchivedItems=0

( and then insert it in your query)

USE EnterpriseVaultDirectory
UPDATE Archive

Where (**Insert here**)
SET ArchiveStatus = 4

I have written some parts out of my head, so check the query before.

Re: EV SQL script to change archive status for all archives with 0 item count

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 Smiley Happy

Re: EV SQL script to change archive status for all archives with 0 item count

That's nice, feel free to post it when it is finished.

Good luck with your query.

Re: EV SQL script to change archive status for all archives with 0 item count

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.

 

--Chris