Share this page

# Obtain database and repository size

This section provides instructions about how to obtain the size of your ALM database and repository.

Every ALM project has its own database schema and file repository. All the steps in this topic should be performed for every project you want to migrate to SaaS.

In this topic:

## Obtain SA database schema name

Follow the steps below to obtain the name of the Site Administration database schema.

1. Log in to the ALM server.

2. Go to the directory where ALM/QC is deployed or installed.

   Linux

   Deployment folder | ALM 12.60 P1 or later: /var/opt/Micro Focus/ALM/ ALM 12.60 or earlier: /var/opt/HP/ALM/
   Installation folder | /opt/Micro Focus/ALM

   Windows

   Deployment folder | C:\Quality Center or C:\QC
   Installation folder | ALM 12.60 P1 or later: C:\Program Files\Micro Focus\ALM ALM 12.60 or earlier: C:\Program Files\HP\ALM

3. Go to the directory where the siteadmin.xml file is saved, and open it in edit mode.

   Linux | /var/opt/Micro Focus/ALM/webapps/qcbin/WEB-INF
   Windows | One of the following: <ALM/QC directory>/webapps/qcbin/WEB_INF <ALM/QC directory>\jboss\server\default\deploy\10sabin.war\WEB-INF <ALM/QC directory>\application\10sabin.war\WEB-INF

4. Search for site admin schema name using the DBNAME tag.

   For example, <DbName>SA_SCHEMA_NAME</DbName>.

Back to top

## Obtain file repository size

Follow the step below to obtain the size of the file repository from the file server.

1. Open ALM Site Administration.

2. Select the project you want to migrate. In the Project Details tab, under the Project Directory field, find the file repository path of the project.

3. On the file server machine, navigate to the repository path of the project and run the following command.

   UNIX

   To summarize the directory and all sub-directories and display size values in a human-readable format, use:

   Copy code

       du –sh <directory name>

   For example,

   Copy code

       root@ubuntu# du -sh /usr/ X11R6

       4.0K /usr/X11R6

   If you find all your path repositories follow the same path, use the following command to show the summary of only the first level of the sub-directories within the folder. This summary lets you know the size of all the file repositories folders on the same path.

   For example,

   Copy code

       root@ubuntu# du -sh /usr/*

       4.0K /usr/X11R6
       96M /usr/bin
       24K /usr/games
       12M /usr/include
       164M /usr/lib
       0 /usr/lib64
       96K /usr/local
       25M /usr/sbin
       197M /usr/share
       4.0K /usr/src

   Linux

   To summarize the directory and all sub-directories and display size values in a human-readable format, use:

   Copy code

       du – sh

   For example,

   Copy code

       $ du -sh svn/ports

       6.2M svn/ports

   Windows

   Right-click the folder and select Properties to view the size of the folder.

Back to top

## Obtain projects information from database

Obtain the number of all projects, the number of projects with version control enabled, and the number of checked-out entities within specific projects.

To obtain the number of all projects:

1. Obtain the Site Administration database schema. See Obtain SA database schema name.

2. Set the SA_SCHEMA parameter to the correct Site Administration schema name.

3. Run the following query:

   Copy code

       SELECT COUNT(*) FROM '<SCHEMA>'.PROEJCTS;

To obtain the number of projects with version control enabled:

1. Obtain the Site Administration database schema. See Obtain SA database schema name.

2. Set the SA_SCHEMA parameter to the correct Site Administration schema name.

3. Run the following query:

   Copy code

       SELECT * FROM PROJECTS WHERE PR_HAS_VCSDB='Y';

To obtain the number of checked-out entities within a specific project:

1. Obtain the project database schema name of the project by doing one of the following:

   - Select the project in the Site Administration > Projects tab. Check the value of the Database Name field.

   - Run the following query on the Site Administration database schema, and from the query output, obtain the value of the DB_NAME field of the project:

     Copy code

         SELECT * FROM PROJECTS

2. Run the following query within the project database schema:

   Oracle server
   Copy code

       Select
       (SELECT count(*) from vc_test) as Tests ,
       (Select count(*) from vc_req) as Requirements,
       (select count (*) from vc_component) as Components,
       (select count(*) from vc_resources) as Resources,
       (Select count(*) FROM VC_ASSET_RELATIONS) as ASSET_RELATIONS ,
       (Select count(*) FROM VC_ASSET_REPOSITORY_ITEMS) as ASSET_REPOSITORY_ITEMS,
       (Select count(*) FROM VC_BPTEST_TO_COMPONENTS) as BPTEST_TO_COMPONENTS,
       (Select count(*) FROM VC_BP_ITERATION) as BP_ITERATION,
       (Select count(*) FROM VC_BP_ITER_PARAM) as BP_ITER_PARAM,
       (Select count(*) FROM VC_BP_PARAM) as BP_PARAM,
       (Select count(*) FROM VC_COMPONENT_MULTIVALUE) as COMPONENT_MULTIVALUE,
       (Select count(*) FROM VC_COMPONENT_STEP) as COMPONENT_STEP,
       (Select count(*) FROM VC_COMPONENT_STEP_PARAMS) as COMPONENT_STEP_PARAMS,
       (Select count(*) FROM VC_CROS_REF) as CROS_REF,
       (Select count(*) FROM VC_DESSTEPS) as DESSTEPS ,
       (Select count(*) FROM VC_FRAMEWORK_PARAM) as FRAMEWORK_PARAM,
       (Select count(*) FROM VC_REQ_MULTIVALUE) as REQ_MULTIVALUE,
       (Select count(*) FROM VC_RUNTIME_PARAM) as RUNTIME_PARAM,
       (Select count(*) FROM VC_STEP_PARAMS) as STEP_PARAMS,
       (Select count(*) FROM VC_TEST_MULTIVALUE) as TEST_MULTIVALUE,
       (Select count(*) FROM VC_TEST_PARAMS) as TEST_PARAMS,
       (Select count(*) FROM VC_USER_ASSETS) as USER_ASSETS,
       (Select count(*) FROM VER_CTRL)
       from dual

   MS SQL server
   Copy code

       Select
       (SELECT count(*) from td.vc_test) as Tests ,
       (Select count(*) from td.vc_req) as Requirements,
       (select count (*) from td.vc_component) as Components,
       (select count(*) from td.vc_resources) as Resources,
       (Select count(*) FROM td.VC_ASSET_RELATIONS) as ASSET_RELATIONS ,
       (Select count(*) FROM td.VC_ASSET_REPOSITORY_ITEMS) as ASSET_REPOSITORY_ITEMS,
       (Select count(*) FROM td.VC_BPTEST_TO_COMPONENTS) as BPTEST_TO_COMPONENTS,
       (Select count(*) FROM td.VC_BP_ITERATION) as BP_ITERATION,
       (Select count(*) FROM td.VC_BP_ITER_PARAM) as BP_ITER_PARAM,
       (Select count(*) FROM td.VC_BP_PARAM) as BP_PARAM,
       (Select count(*) FROM td.VC_COMPONENT_MULTIVALUE) as COMPONENT_MULTIVALUE,
       (Select count(*) FROM td.VC_COMPONENT_STEP) as COMPONENT_STEP,
       (Select count(*) FROM td.VC_COMPONENT_STEP_PARAMS) as COMPONENT_STEP_PARAMS,
       (Select count(*) FROM td.VC_CROS_REF) as CROS_REF,
       (Select count(*) FROM td.VC_DESSTEPS) as DESSTEPS ,
       (Select count(*) FROM td.VC_FRAMEWORK_PARAM) as FRAMEWORK_PARAM,
       (Select count(*) FROM td.VC_REQ_MULTIVALUE) as REQ_MULTIVALUE,
       (Select count(*) FROM td.VC_RUNTIME_PARAM) as RUNTIME_PARAM,
       (Select count(*) FROM td.VC_STEP_PARAMS) as STEP_PARAMS,
       (Select count(*) FROM td.VC_TEST_MULTIVALUE) as TEST_MULTIVALUE,
       (Select count(*) FROM td.VC_TEST_PARAMS) as TEST_PARAMS,
       (Select count(*) FROM td.VC_USER_ASSETS) as USER_ASSETS,
       (Select count(*) FROM td.VER_CTRL) as VER_CTRL

Back to top

## Obtain project database schema size

Follow the steps below to obtain the size of the project database schema.

1. Open ALM Site Administration.

2. Select the project you want to migrate. In the Project Details tab, under the Database Name field, find the database schema of the project. Under the Database Server field, find the server where the schema is located.

3. On the database server, run the following command to obtain the size of the database schema.

   Oracle server

   To obtain the size of the DB_USER_SCHEMA schema, connect to the Oracle instance and run the following SQL query.

   - To obtain the size of each database schema, run:

     Copy code

         SELECT SUM(BYTES)/1024/1024 "MB" FROM DBA_SEGMENTS WHERE owner='<SCHEMA>';

   - To obtain the size of all database schemas at one time, run:

     Copy code

         SELECT owner, SUM(BYTES)/1024/1024 "MB" FROM DBA_SEGMENTS group by owner

   MS SQL Server

   Run the following command to print a summary of space used in the current database.

   Copy code

       sp_spaceused

   Output:

   Copy code

       database_name database_size
       --------------- ---------------------------
       master 5 MB
       reserved data index_size unused
       --------- --------- ----------- --------
       2176 KB 1374 KB 72 KB 730 KB

   Note: The database_size metric shows the size of the current database in megabytes, including both data and log files.

Back to top

## Obtain database and repository size of all projects

Obtain all the projects details including their database schema and file repository size as stored in the database based on the smart repository mechanism.

This section is applicable only to ALM 11.00 and later that includes the smart repository mechanism.

Prerequisites:

1. Obtain the Site Administration database schema. See Obtain SA database schema name.

2. Set the SA_SCHEMA parameter to the correct Site Administration schema name.

To obtain database and repository size of all projects:

Run the following query:

Oracle
Copy code

    SET serveroutput ON size unlimited;
    DECLARE
      sa_name      VARCHAR2(50 CHAR) := '<SA_SCHEME>';
      table_exists NUMBER;
      create_table LONG;
    TYPE cur_type
    IS
      REF
      CURSOR;
        schema_cur cur_type;
        project_schemas_cur cur_type;
        projects_query LONG;
        project_size_query LONG;
        project_schema VARCHAR2(100 CHAR) ;
        project_name   VARCHAR2(100 CHAR) ;
        domain_name    VARCHAR2(100 CHAR) ;
        repo_path      VARCHAR2(100 CHAR) ;
        is_active      VARCHAR2(1 CHAR) ;
        is_template    VARCHAR2(1 CHAR) ;
        db_size        INTEGER;
    BEGIN
        projects_query := 'select p.domain_name, p.project_name, p.db_name, p.physical_directory, p.pr_is_active, p.IS_TEMPLATE , t.db_size_mb
    from ' || sa_name || '.projects p join (SELECT round(sum(bytes)/1024/1024,2) as db_size_mb, owner
    FROM dba_segments WHERE lower(owner) in (select lower(db_name) from ' || sa_name || '.projects) group by owner) t  on lower(t.owner) = lower(p.db_name) ';
        dbms_output.put_line(projects_query);
        create_table:='
    CREATE  TABLE project_size_report
    (Domain VARCHAR2(50 CHAR),
    project VARCHAR2(50 CHAR),
    db_name  VARCHAR2(50 CHAR),
    physical_directory   VARCHAR2(100 CHAR),
    is_active  VARCHAR2(1 CHAR),
    is_template  VARCHAR2(1 CHAR),
    count_files integer,
    repo_size_mb number,
    db_size_mb VARCHAR2(50 CHAR)
    )';
        SELECT COUNT(*)
        INTO table_exists
        FROM dba_tables
        WHERE lower(table_name) = 'project_size_report';
      IF (table_exists         <> 0) THEN
        EXECUTE immediate 'drop TABLE project_size_report';
        COMMIT;
      END IF;
      EXECUTE immediate create_table;
      COMMIT;
      OPEN project_schemas_cur FOR projects_query;
      <<main_loop>>
      LOOP
        FETCH project_schemas_cur
        INTO domain_name,
          project_name,
          project_schema,
          repo_path,
          is_active,
          is_template,
          db_size ;
        IF project_schemas_cur%found THEN
          project_size_query := 'insert into project_size_report (Domain, project, db_name , physical_directory , is_active ,  is_template ,   count_files, repo_size_mb , db_size_mb)
    Select ' || '''' || domain_name || '''' || 'as Domain, ' || '''' || project_name || '''' || ' as project,  ' || '''' || project_schema || '''' || ' as db_name, ' || '''' || repo_path || '''' || ' as physical_directory, ' || '''' || is_active || '''' || ' as is_active, ' || '''' || is_template || '''' || ' as is_template,  count_files, project_repo_size_mb, ' || '''' || db_size || '''' || ' as db_size_mb from  (select count(*) as count_files , round(sum(t.sz),2)  as project_repo_size_mb from  (select distinct srlf_physical_id, srlf_size/1024/1024 as sz
    from  '|| project_schema || '.smart_repository_physical_file inner join  ' || project_schema ||'.smart_repository_logical_file  on  srlf_physical_id = srpf_id )t  )  ';
          dbms_output.put_line('running over project ' || domain_name || ' / ' || project_name);
          dbms_output.put_line(project_schemas_cur%ROWCOUNT);
          EXECUTE immediate project_size_query;
          COMMIT;
        ELSE
          EXIT;
        END IF;
      END LOOP main_loop;
      CLOSE project_schemas_cur;
      dbms_output.put_line('Finished script, please run: select * from project_size_report');
    END;
    /
    --- WHEN IT FINISHES RUN, YOU DO SELECT FROM THIS TABLE
    -------     select * from project_size_report;

MS SQL
Copy code

    USE [master]
    GO
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    /*
      NOTE! you will have to find your value for the site admin db schema (sa_name).
      The default Site Administration database schema name is "qcsiteadmin_db"
      but it could have been changed when ALM/QC was installed
      Check the value <DbName> in siteadmin.xml under the deloyment directory,
      by default: C:\ProgramData\HP\ALM\webapps\qcbin\WEB-INF\siteadmin.xml
    */

    begin

    declare @sa_name varchar(100)
    declare @report_table varchar(100)
    declare @db_name varchar(100)
    declare @n_repo_count nvarchar(max)
    declare @n_repo_size  nvarchar(max)
    declare @n_db_size  nvarchar(max)
    declare @query nvarchar(max)
    declare @message varchar(500)
    USE [master]
    GO
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    /*
      NOTE! you will have to find your value for the site admin db schema (sa_name).
      The default Site Administration database schema name is "qcsiteadmin_db"
      but it could have been changed when ALM/QC was installed
      Check the value <DbName> in siteadmin.xml under the deloyment directory,
      by default: C:\ProgramData\HP\ALM\webapps\qcbin\WEB-INF\siteadmin.xml
    */

    begin

    declare @sa_name varchar(100)
    declare @report_table varchar(100)
    declare @db_name varchar(100)
    declare @n_repo_count nvarchar(max)
    declare @n_repo_size  nvarchar(max)
    declare @n_db_size  nvarchar(max)
    declare @query nvarchar(max)
    declare @message varchar(500)
    end

      exec ('CREATE TABLE ' + @report_table +
       '(
        "PROJECT_NAME" varchar(255),
        "DOMAIN_NAME" varchar(255),
        "DB_NAME" varchar(60),
        "PHYSICAL_DIRECTORY" varchar(255),
        "PR_IS_ACTIVE" varchar(2),
        "SEND_MAIL" varchar(2),
        "PR_HAS_VCSDB" varchar(2),
        "IS_TEMPLATE" varchar(2),
        "DB_SIZE" varchar(255),
        "REPO_SIZE" varchar(255),
        "REPO_COUNT" varchar(255)
       )')
          SET @message =  'Creating the report table '
    + @report_table   RAISERROR( @message, 10,1) WITH NOWAIT

      end

    set @query = N'USE ' + @sa_name + + N'; declare objcursor CURSOR FOR select db_name from td.projects'
    exec sys.sp_executesql     @query

    open objcursor

    fetch next from objcursor into @db_name
     while @@FETCH_STATUS = 0

    begin

    set @query = 'use master; SELECT @n_db_size = sum((size * 8) / 1024) FROM sys.master_files WHERE DB_NAME(database_id) = '''
                +@db_name+ ''' group by database_id'

       EXEC sp_executesql @query, N'@n_db_size varchar(max) OUTPUT', @n_db_size = @n_db_size OUTPUT

    set @query = 'use ' + @db_name + ';  select @n_repo_count = count(*) , @n_repo_size = round(sum(t.sz),2) from  ' +
            '(select distinct srlf_physical_id, srlf_size/1024/1024 sz from  td.smart_repository_physical_file ' +
            'inner join  td.smart_repository_logical_file  on  srlf_physical_id = srpf_id) t '

           EXEC sp_executesql @query,
                       N'@n_repo_count varchar(255) OUTPUT, @n_repo_size varchar(max) OUTPUT',
                       @n_repo_count = @n_repo_count OUTPUT, @n_repo_size = @n_repo_size OUTPUT

    set @query = 'use ' + @sa_name + '; insert into master..project_size_report select project_name, domain_name, db_name, '
    + 'physical_directory, pr_is_active, saq_is_active, pr_has_vcsdb, is_template, '''+ @n_db_size +
    + ''', ''' + @n_repo_size + ''' , ''' + @n_repo_count +  ''' from td.projects where db_name = ''' + @db_name + ''''

    exec sp_executesql @query

    fetch next from objcursor into @db_name
    end

    close objcursor
    deallocate objcursor
    end;

    --  This script creates a table 'project_size_report' with the required information.
    --  Export the table in Excel or CSV format and send it back to your SaaS CSM:
    --select * from master..project_size_report /* <@report_table> */

    --  Delete the table afterward if you wish:
    --drop table master..project_size_report /* <@report_table> */

Back to top

## Export database schema

Export the database schema of each project you want to migrate.

Prerequisites

Consider the following prerequisites before you export the database schema.

AWS S3 Bucket | An AWS S3 Bucket location should be provided by your CSM during the migration process.
MD5 software | For example, md5.
FTP server | Only when AWS S3 Bucket is not available for some reason, a FTP server location should be provided by your CSM during the migration process.

To export the database schema:

Perform one of the following export actions, depending on your database type:

Oracle

1. Connect to the database server as a database user.

2. Run the following command for each schema you want to export.

   Copy code

       expdp system/<password> schemas=<schema name> dumpfile=<schema name>_<date>.dmp logfile=<schema
       name>_<date>.log exclude=statistics directory=DATA_PUMP_DIR

3. Connect to the database as a system user, for example, `sqlplus system/<password>`, and run the following command to loate your data dump directory:

   Copy code

       SELECT directory_path FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR';

4. Change to the directory, and you will see all the dump files.

5. Compress the backups of the database schemas.

6. Run `md5sum`.

7. Open a command window, navigate to the location where the md5.zip file was extracted, and run the following command.

   Copy code

       md5.exe -generate <compressed folder path><compressed folder name.zip>

   For example,

   Copy code

       md5.exe – generate C:\”Documents and Settings”\Desktop\ project1_filereposiotry.zip

   The command returns the md5sum output. For example,

   Copy code

       24BCED0C939DD7E61C6E4CCE252687BE db_schemas.zip

8. Copy the output to a text file.

9. Upload the compressed database schema and the text file to the FTP server.

MS SQL Server

1. Back up the database schemas by one of the following options.

   - Back up each database schema by running:

     Copy code

         Backup database X to disk=’<path>’

   - Detach each database schema by running:

     Copy code

         Sp_detach_db ‘database name’

2. Compress the backups of the database schemas.

3. Run `md5sum`.

4. Open a command window, navigate to the location where the md5.zip file was extracted, and run the following command.

   Copy code

       md5.exe -generate <compressed folder path><compressed folder name.zip>

   For example,

   Copy code

       md5.exe – generate C:\”Documents and Settings”\Desktop\ project1_filereposiotry.zip

   The command returns the md5sum output. For example,

   Copy code

       24BCED0C939DD7E61C6E4CCE252687BE db_schemas.zip

5. Copy the output to a text file.

6. Upload the compressed database schema and the text file to the FTP server.

Back to top

Next steps:

- Export ALM/QC projects

https://admhelp.microfocus.com ALM 8942
Tips to improve your search results... ALM Help Center ALM Help Center ALM Help 16.00-16.0.1 All View Local Help Center View Online Help Center back Go back in browser history forward Go forward in browser history Remove search highlights next topic Go to next topic previous topic Go to previous topic Print the current topic Version: