How to get total size of a repository or project

Document ID : KB000095594
Last Modified Date : 11/05/2018
Show Technical Document Details
Question:
Is there an easy way that one can tell the size in KB/MB/GB of a specific Harvest repository?
Environment:
CA Harvest SCM all versions and platforms
 
Answer:
If you know the repository name (top level folder name) this should do it: 

SELECT SUM(HARVERSIONDATA.DATASIZE) TOTAL_SIZE 
FROM HARVERSIONS 
INNER JOIN HARVERSIONDATA 
ON HARVERSIONS.VERSIONDATAOBJID = HARVERSIONDATA.VERSIONDATAOBJID 
WHERE HARVERSIONS.VERSIONOBJID IN ( 
  SELECT DISTINCT HARVERSIONS.VERSIONOBJID 
  FROM HARREPOSITORY 
  INNER JOIN HARITEMS 
  ON HARREPOSITORY.REPOSITOBJID = HARITEMS.REPOSITOBJID 
  INNER JOIN HARVERSIONS 
  ON HARITEMS.ITEMOBJID = HARVERSIONS.ITEMOBJID 
  WHERE HARREPOSITORY.REPOSITNAME = 'SampleRepository' /* <---- Repository Name goes here */

… If your repository is shared by two or more projects, this will show the combined size for all projects sharing the repository. 

If you want to go by project, try this: 

SELECT SUM(HARVERSIONDATA.DATASIZE) TOTAL_SIZE 
FROM HARVERSIONS 
INNER JOIN HARVERSIONDATA 
ON HARVERSIONS.VERSIONDATAOBJID = HARVERSIONDATA.VERSIONDATAOBJID 
WHERE HARVERSIONS.VERSIONOBJID IN ( 
  SELECT DISTINCT HARVERSIONINVIEW.VERSIONOBJID 
  FROM HARENVIRONMENT 
  INNER JOIN HARVIEW 
  ON HARENVIRONMENT.ENVOBJID = HARVIEW.ENVOBJID 
  INNER JOIN HARVERSIONINVIEW 
  ON HARVIEW.VIEWOBJID = HARVERSIONINVIEW.VIEWOBJID 
  WHERE HARENVIRONMENT.ENVIRONMENTNAME = 'SampleProject' /* <---- Project Name goes here  */


Both of the above queries will return 1 row with 1 numeric value - the total size of all versions for that repository or project. To get a listing of the total size for all projects, try this: 

SELECT R.ENVIRONMENTNAME, SUM(R.DATASIZE) TOTALSIZE 
FROM ( 
  SELECT DISTINCT HARENVIRONMENT.ENVIRONMENTNAME, 
  HARVERSIONS.VERSIONOBJID, 
  HARVERSIONDATA.DATASIZE 
  FROM HARENVIRONMENT 
  INNER JOIN HARVIEW 
  ON HARENVIRONMENT.ENVOBJID = HARVIEW.ENVOBJID 
  INNER JOIN HARVERSIONINVIEW 
  ON HARVIEW.VIEWOBJID = HARVERSIONINVIEW.VIEWOBJID 
  INNER JOIN HARVERSIONS 
  ON HARVERSIONS.VERSIONOBJID = HARVERSIONINVIEW.VERSIONOBJID 
  INNER JOIN HARVERSIONDATA 
  ON HARVERSIONS.VERSIONDATAOBJID = HARVERSIONDATA.VERSIONDATAOBJID 
  WHERE LENGTH(HARENVIRONMENT.ENVIRONMENTNAME) > 1 
) R 
GROUP BY R.ENVIRONMENTNAME
Additional Information:
SQL Queries can be run by using tools specific to your type of database, for example, SQL Plus for Oracle or SQL Server Management Studio for SQL Server.  Or you can use the SCM command line tool "hsql" to execute these queries and get the result.

For more information on the SCM command line utility "hsql" you can consult the SCM documentation website here:  https://docops.ca.com/ca-harvest-scm/13-0/en/command-reference/get-started-with-ca-harvest-scm-commands/hsql-command-rdb-query

For more information on tools specific to your type of database, consult the documentation for your database.