Forum Discussion

CadenL's avatar
CadenL
Level 6
6 years ago

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

  • 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

    • CadenL's avatar
      CadenL
      Level 6

      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

      • GertjanA's avatar
        GertjanA
        Moderator

        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.

  •  

    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.

    • CadenL's avatar
      CadenL
      Level 6

      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 :smileyhappy:

      • CConsult's avatar
        CConsult
        Moderator

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

        Good luck with your query.

  • 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