SQL Database growing over the size of 10Gb

Hi!

It would be useful to know what tables are the top disk consumers. Perhaps, you could configure stricter clean-up policies to clean the builds more aggressively. But please note that currently some tables are not cleaned up, e.g., the vcs_change table. There's a related task in our tracker: TW-25099.

Cheers,
-Anatoly 

Hi Anatoly,

Thanks for your reply. I launched a request in the database to get the size used by each tables of the Database. The table test_names consume 10Gb of the space. Please have a look to the screenshot below.

Thanks in advance for your help

Stephane

Stephane,

The test_names table contains all test names seen by TeamCity server in your builds. TeamCity should clean it up according to the Clean-up Rules in the project settings. 6.8 million rows are quite a lot, meaning most likely, the builds running the tests are not cleaned up.

The SQL query below should shown you which build configurations have the most test names associated with them. It would make sense to look at the clean-up rules applied to those configurations. Please, ensure they are strict enough and work as expected.

SELECT
  build_type_mapping.ext_id as build_configuration_id,
  tests_per_bt.total_test_count as total_test_names
FROM
  (
    SELECT
      history.build_type_id,
      SUM(tests_per_build.test_count) as total_test_count
    FROM
      (
        SELECT
          test_info.build_id,
          COUNT(test_names.id) as test_count
        FROM
          test_info
          INNER JOIN test_names ON test_info.test_name_id = test_names.id
        GROUP BY
          test_info.build_id
      ) as tests_per_build
      INNER JOIN history ON history.build_id = tests_per_build.build_id
    GROUP BY
      history.build_type_id
  ) as tests_per_bt
  INNER JOIN build_type_mapping ON build_type_mapping.int_id = tests_per_bt.build_type_id
ORDER BY
  total_test_names DESC

-Anatoly 

Hi Anatoly,

I ran the SQL request and got the id of the build configuration that contains most of the test_names.

Then I had a look to the clean-up rules of the project and it is set as the other projects.

Any advise on this will appreciated.

Stephane

Stephane,

Apparently, clean-up isn't removing the build history for some reason. Besides not having enough successful builds in a branch to satisfy the criteria specified in your clean-up rule, the builds won't be cleaned up if:

1. The build is pinned.
2. The build is a dependency for another build in the chain. You can tell that by the presence of the chain link icon on the build in the build history, or using these SQL selects:

SELECT * FROM build_dependency WHERE depends_on = <build_id>;
SELECT * FROM downloaded_artifacts WHERE source_build_id = <build_id>;

-Anatoly 

Anatoly,

I ran your two last queries, and there are no depency chain for the project, and no builds are pinned.

It seems that the Clean-up do some work. It ran this morning and I re-ran your SQL request and here is the result:

So it seems that records are being removed but the size of database is still growing. Please have a look to the size used by the table compared to my first message.

Thanks for your help

Stephane

Stephane,

I'd recommend taking a look at the build history and making sure your clean-up policies work as expected, i.e. there aren't any unexpectedly old builds in the build configuration history. If the policies do in fact work as expected, you might need to make them stricter to keep even fewer builds in history.

-Anatoly 

Hello,

Maybe someone will have similar problem, so i will leave my findings here.

I had the same problem and after some investigation, i found out that many of rows in dbo.test_names table were “ghost” rows (table dbo.test_info was missing info for rows). Because of it, i think clean-up policies weren't able to clean up all data from database.

Running following query:

SELECT
    CASE
        WHEN ti.test_name_id IS NULL THEN NULL
        ELSE 'NOT NULL'
    END as chck
    ,COUNT(*) cnt
FROM dbo.test_names as tn
    LEFT JOIN dbo.test_info as ti
        ON ti.test_name_id = tn.id
GROUP BY 
    CASE
        WHEN ti.test_name_id IS NULL THEN NULL
        ELSE 'NOT NULL'
    END
 

returned 4 509 562 rows:

chck                cnt
NOT NULL    1308037
NULL             4509562

in my opinion all 4509562 rows can be deleted safely. If you want to delete the data, do a backup first! 

If anyone has problem with TeamCity database size, please check above SQL if you have similar issue.